Bright01
asked on
Simple Addition to Existing Macro
EE Pros,
I have a nice little Macro that watches for a seletion from a list that when it is selected, it automatically displays the appropriate Tab. There are actually 2 selections (N22 and N23 are the cells that change). And when the cells are cleared, the Tab goes away. It works. However, now what I want is that when I select a particular list variable (
in N23), I want to display several tabs that are hidden... not just the one selected from the list. How do I enter a line of code that adds the other Hidden Tabs and how do they vanish if the cell (N23) is blank?
Thank you in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim TabToHide As Variant
If Target.Address = Range("N22").Address Then
On Error Resume Next
If Target.Value = "" Then
For Each ws In ThisWorkbook.Worksheets
TabToHide = Application.Match(ws.Name, Range("Tablist"), 0)
If IsNumeric(TabToHide) Then
Sheets(ws.Name).Visible = False
Sheet20.Visible = xlSheetVisible
Sheet20.Activate
End If
Next ws
End If
Sheets(Target.Value).Visib le = True
On Error GoTo 0
End If
If Target.Address = Range("N23").Address Then
On Error Resume Next
If Target.Value = "" Then
For Each ws In ThisWorkbook.Worksheets
TabToHide = Application.Match(ws.Name, Range("Typelist"), 0)
If IsNumeric(TabToHide) Then
Sheets(ws.Name).Visible = False
Sheet20.Visible = xlSheetVisible
Sheet20.Activate
End If
Next ws
End If
Sheets(Target.Value).Visib le = True
On Error GoTo 0
End If
End Sub
I have a nice little Macro that watches for a seletion from a list that when it is selected, it automatically displays the appropriate Tab. There are actually 2 selections (N22 and N23 are the cells that change). And when the cells are cleared, the Tab goes away. It works. However, now what I want is that when I select a particular list variable (
in N23), I want to display several tabs that are hidden... not just the one selected from the list. How do I enter a line of code that adds the other Hidden Tabs and how do they vanish if the cell (N23) is blank?
Thank you in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim TabToHide As Variant
If Target.Address = Range("N22").Address Then
On Error Resume Next
If Target.Value = "" Then
For Each ws In ThisWorkbook.Worksheets
TabToHide = Application.Match(ws.Name,
If IsNumeric(TabToHide) Then
Sheets(ws.Name).Visible = False
Sheet20.Visible = xlSheetVisible
Sheet20.Activate
End If
Next ws
End If
Sheets(Target.Value).Visib
On Error GoTo 0
End If
If Target.Address = Range("N23").Address Then
On Error Resume Next
If Target.Value = "" Then
For Each ws In ThisWorkbook.Worksheets
TabToHide = Application.Match(ws.Name,
If IsNumeric(TabToHide) Then
Sheets(ws.Name).Visible = False
Sheet20.Visible = xlSheetVisible
Sheet20.Activate
End If
Next ws
End If
Sheets(Target.Value).Visib
On Error GoTo 0
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
B.