VBA to Hide/Unhide a Tab Based on Contents of Another Cell

[V L]

Hello All:

I have a spreadsheet with multiple tabs. Range A2 through A1000 contains 5-digit numbers. These 5-digit numbers correspond to tabs of the same name. In column B, I would like to create a situation where placing an "X" in a cell (let’s say placing an X in B2) would cause the tab referenced in cell A2 to hide. Then again, removing the X from B2 would cause the tab referenced in A2 to unhide. I would like to run this as a macro at will. Any assistance is appreciated...

 

[sxhall]

This will do the above...

Sub HideSheet()

Dim ShtNm As String

For Each i In Range("B2:B1000")
ShtNm = i.Offset(0, -1).Value

If i.Value = "X" Then
    Sheets(ShtNm).Visible = False
Else
    Sheets(ShtNm).Visible = True
End If

Next i

End Sub

 

[V L]

This will do the above...

Sub HideSheet()

Dim ShtNm As String

For Each i In Range("B2:B1000")
ShtNm = i.Offset(0, -1).Value

If i.Value = "X" Then
    Sheets(ShtNm).Visible = False
Else
    Sheets(ShtNm).Visible = True
End If

Next i

End Sub

Thank you for the assistance. Getting a "subscript out of range" error. Any suggestions?

 

[sxhall]

THis is how my test data looked....

Is the text in column A 'exactly' the same as the sheet names? For example if I have sheet name 222 in my list but no tab with this name then I get your error message.

 

[V L]

THis is how my test data looked....

View attachment 115474

Is the text in column A 'exactly' the same as the sheet names? For example if I have sheet name 222 in my list but no tab with this name then I get your error message.

I checked that. Tabs are named exactly as the sheet names. The code works otherwise...

 

[sxhall]

Can't find another way to get the same error as you are experiencing.

Can you load up an image of your sheet or host the workbook somewhere and I'll take a look.