Solved

Simple Addition to Existing Macro

Posted on 2014-04-20
2
137 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 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now