Solved

Simple Addition to Existing Macro

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

806 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