locked
A search box to search for a worksheet in Excel RRS feed

  • Question

  • Hi,

    Can anyone let me know how to create a search box which would search for a certain worksheet and display that particular sheet in Excel file? Say I've like 45 worksheets and want to search for the sheet by it's name and display that particular sheet. Consider me as a tyro in creating macros and jargon less answer would be great. 

    Thanks for all your help. 

    Thursday, August 16, 2018 11:56 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, August 17, 2018 1:07 AM
  • Hello Sai,

    Based on my test, please try the VBA code as below:

    Sub FindWS()
        Dim strWSName As String
        
        strWSName = InputBox("Enter the sheet name to serach for")
        If strWSName = vbNullString Then
            MsgBox "You cancelled!"
            Exit Sub
        End If
        
        If SheetExists(strWSName) Then
            Worksheets(strWSName).Activate
        Else
            MsgBox "That sheet name does not exist!"
        End If
        
    End Sub
    
    Function SheetExists(strWSName As String) As Boolean
        Dim ws As Worksheet
        On Error Resume Next
        Set ws = Worksheets(strWSName)
        If Not ws Is Nothing Then SheetExists = True
    End Function
    

    Hope it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Wouter Defour Friday, August 17, 2018 9:54 AM
    Friday, August 17, 2018 6:02 AM
  • Thanks for you asking. Did you resolved your issue ? If the post helps you, you can mark it as answer and close the post.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Wednesday, August 22, 2018 6:33 AM