Unhide sheets in a workbook by imputing a value with like name in a cell

[racefanjtd]

Hello,

I have a workbook with a bunch of sheets named with 5-digit numbers, for example 13219. I would like all of the sheets to be hidden except for a sheet with an input box to input the 5-digit number. When a number is present, the corresponding sheet will also be visible. Is this possible?

 

[DanteAmor]

Hi and welcome to the board.

Try this.
Change in the macro the word "Main" to the name of your sheet that will remain visible

Sub UnhideSheets()
  Dim sName As Variant
  Dim sh As Worksheet
 
  sName = InputBox("Unhide Sheet:")
  If sName = "" Then Exit Sub
 
  If Evaluate("ISREF('" & sName & "'!A1)") = False Then
    MsgBox "The sheet does not exist"
    Exit Sub
  End If
 
  For Each sh In Sheets
    If sh.Visible = xlSheetVisible Then
      If sh.Name <> "Main" Then
        sh.Visible = xlSheetHidden
      End If
    End If
  Next
 
  Sheets(sName).Visible = xlSheetVisible
End Sub

 

[racefanjtd]

Hello Dante, thanks for the welcome and the reply. I wish I had found this board sooner.

I tried your code, and it didn't work, I probably should have been more specific on some details.

Coincidentally, the Main sheet for my workbook is named Main. I have many additional sheets, almost all of which are named with a 5-digit number.

On the main page, cell E2 is the input cell, to input one of the 5-digit numbers. When a number is put in that cell, I want the Main sheet, and the sheet with the matching number to E2 to be visible.

 

[DanteAmor]

I probably should have been more specific on some details.

And yet, more data is missing.

I will try to guess that you want to capture the sheet number in cell E2 and after pressing Enter, the sheet will automatically be displayed.

Put the following code in the events of the Main sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address = "$E$2" Then
    If Target.Value = "" Then Exit Sub
    
      Dim sName As Variant
      Dim sh As Worksheet
      
      sName = Target.Value
      
      If Evaluate("ISREF('" & sName & "'!A1)") = False Then
        MsgBox "The sheet does not exist"
        Exit Sub
      End If
      
      For Each sh In Sheets
        If sh.Visible = xlSheetVisible Then
          If sh.Name <> "Main" Then
            sh.Visible = xlSheetHidden
          End If
        End If
      Next
      
      Sheets(CStr(sName)).Visible = xlSheetVisible

  End If
End Sub

Note Sheet Event:
Right click the tab "Main" you want this to work, select view code and paste the code into the window that opens up.

 

[racefanjtd]

Awesome, thank you. That works exactly as I had hoped. I input the 5-digit number in cell E2 and hit enter, the sheet that corresponds to that number is now visible.

 

[jolivanes]

The problem I see with that is the possibility of errors. It is easy to type in a wrong number, accidently adding a leading or trailing space or even a space where it should not be.
The "Code does work" is a repeating sentence in these sites where it turns out to be something like mentioned above.
I prefer to use the computer to give me a choice of sheet names to choose from. Easy to do with a Userform/Listbox.
But of course if a solution by typing in a name is wanted, that is the way to help.