Solved Macro to Unhide sheet with input box

October 19, 2012 at 05:53:59
Specs: Windows XP
I am looking to create a Macro that will unhide any sheet that has one specific name. To search for the name i would like an input box to appear to enter the name and then up hitting OK the sheets that contain that word in the name should appear. For example I click a button labeled Search then the input box appear asking what name to search for, then i type in Case and then any sheet with the Name "Case" in the name of the sheet should be unhidden. Thanks for the help

THis is what i have so far and the inputbox appears:
Sub UnhideClient()
Clientsearch = InputBox("Enter Name of Client")

End Sub

See More: Macro to Unhide sheet with input box

Report •

October 19, 2012 at 08:33:51
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.

As for your question, this may be more than you asked for, so feel free to modify it as you see fit. If nothing else, perhaps you will learn a little more about VBA in the process, which is never a bad thing. ;-)

You should consider using Application.InputBox instead of just InputBox. Application.InputBox allows you to check for the Cancel button, which sets the response to False, so that you can make decisions (e.g. Exit Sub) based on the response. If you use just InputBox, the code continues to run even if the response was Cancel. This can often cause a problem.

Ok, this is what this code does:

1 - It hides all sheets except for the one named "Stay Visible". You must always have at least 1 sheet visible, so choose the one you want to always see.
2 - It requests a string from the user.
3 - It loops through the sheets Unhiding any sheet with that string in the sheet name.
4 - If the string does not exist in any sheet name, it asks the user if (s)he wants to try again. If the answer is Yes, it loops back to get the input from the user. If the answer is No, the code exits.

You should always have some means of handling bad input so that your code doesn't crash or do strange things if the input is not what you expected to get. You just can't trust those darn users. ;-)

Note: As written, the does not hide the "Always Visible" sheet ever. Obviously, this could be modified.

Sub UnhideClient()
'Hide all sheets except 1
 For sht = 1 To Sheets.Count
  If Sheets(sht).Name <> "Stay Visible" Then Sheets(sht).Visible = False
 Next sht
'Get Sheet Name
  ClientSearch = Application.InputBox("Enter Name of Client")
'Exit on Cancel
    If ClientSearch = False Then Exit Sub
'Unhide Sheet if string found, set "Found" flag
      For sht = 1 To Sheets.Count
          If Sheets(sht).Name Like "*" & ClientSearch & "*" Then
            Sheets(sht).Visible = True
            ShtFound = "OK"
          End If
      Next sht
'If Found flag not set, Ask user to try again
      If ShtFound <> "OK" Then
        answer = MsgBox("The Following String Was Not Found:" & vbCrLf & vbCrLf & _
                        ClientSearch & vbCrLf & vbCrLf & _
                        "Do you want to try again?", vbYesNo, "Invalid Sheet Name")
                 If answer = vbYes Then GoTo getInput
       End If
End Sub should read the following How To. It might help you understand the code I've posted as well as any other code that you find on the web. There's a lot of great stuff out there and getting down into the details is much better than just simply running the code without fully understanding it. I learned an huge amount about VBA by following the suggestions outlined in the following How To.

Come on back if you have any questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question