Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Simple Addition to Existing Macro

Posted on 2014-04-20
2
Medium Priority
?
160 Views
Last Modified: 2014-04-25
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).Visible = 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).Visible = True
        On Error GoTo 0
    End If
End Sub
0
Comment
Question by:Bright01
2 Comments
 
LVL 16

Accepted Solution

by:
Peter Kwan earned 2000 total points
ID: 40011971
You may update as this:

    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
        Else
            For Each ws In ThisWorkbook.Worksheets
                If Sheets(ws.Name).Visible = False And ws.Name <> Target.Value Then
                  Sheets(ws.Name).Visible = True
                End If
            Next ws
        End If
        Sheets(Target.Value).Visible = True
        On Error GoTo 0
    End If

Open in new window

0
 

Author Closing Comment

by:Bright01
ID: 40022732
Pkwan,  Thank you!  Works great.  Sorry for the delay.......

B.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question