OzGrid

How to reference text in cell to unhide worksheet

< Back to Search results

 Category: [Excel]  Demo Available 

How to reference text in cell to unhide worksheet

 

Requirement:

 

The user is trying to accomplish the following in VBA/Macros:

 

Selecting “Yes” in cell AW5 (or AW10, etc.) will unhide the worksheet named whatever the text contained in B5, B10, etc. is.

 

All other sheets will remain hidden unless “Yes” is selected in the corresponding cell.

 

Here is what the user has so far:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [AW5] = "Yes" Then
Sheets(Range(B5)).Visible = True

Else
Sheets(Range(B5)).Visible = False

End If
End Sub

 

Solution:

 

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s As String
    If Intersect(Target, Range("AW:AW")) Is Nothing Then Exit Sub
    s = Cells(Target.Row, 2)
    If Target = "Yes" Then
        Sheets(s).Visible = True
    Else
        Sheets(s).Visible = False
    End If
End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.

 

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1215369-reference-text-in-cell-to-unhide-worksheet

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to reference a cell that contains a word to into a cell that has a sentence
How to change reference columns in another worksheet using VBA looping
How to use VBA code using relative references
How to use VBA code to reference cell to another sheet

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 


Gallery



stars (0 Reviews)