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.
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
