+ Reply to Thread
Results 1 to 5 of 5

alternative for (multiple) worksheet change

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: alternative for (multiple) worksheet change

    There's a couple of ways to approach this. You can write a sub for each case and just call that sub. I notice that most everything is set to visible = false so why not set everything false, and then in each case just set visible = true for only the items that need to be visible.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
            If Target.Address = "$F$6" Then
                ActiveSheet.Shapes("tombstone-1").Visible = False
                ActiveSheet.Shapes("inputTS").Visible = False
                ActiveSheet.Shapes("4-2-1").Visible = False
                ActiveSheet.Shapes("8-2-1").Visible = False
                ActiveSheet.Shapes("11-2-1").Visible = False
                ActiveSheet.Shapes("14-2-1").Visible = False
                ActiveSheet.Shapes("17-2-1").Visible = False
                ActiveSheet.Shapes("20-2-1").Visible = False
                ActiveSheet.Shapes("23-2-1").Visible = False
                ActiveSheet.Shapes("26-2-1").Visible = False
                ActiveSheet.Shapes("8-4-1").Visible = False
                ActiveSheet.Shapes("11-4-1").Visible = False
                ActiveSheet.Shapes("14-4-1").Visible = False
                ActiveSheet.Shapes("17-4-1").Visible = False
                ActiveSheet.Shapes("20-4-1").Visible = False
                ActiveSheet.Shapes("23-4-1").Visible = False
                ActiveSheet.Shapes("26-4-1").Visible = False
                ActiveSheet.Shapes("11-8-1").Visible = False
                ActiveSheet.Shapes("12-8-1").Visible = False
                ActiveSheet.Shapes("14-8-1").Visible = False
                ActiveSheet.Shapes("15-8-1").Visible = False
                ActiveSheet.Shapes("17-8-1").Visible = False
                ActiveSheet.Shapes("18-8-1").Visible = False
                ActiveSheet.Shapes("20-8-1").Visible = False
                ActiveSheet.Shapes("21-8-1").Visible = False
                ActiveSheet.Shapes("23-8-1").Visible = False
                ActiveSheet.Shapes("24-8-1").Visible = False
                ActiveSheet.Shapes("26-8-1").Visible = False
                ActiveSheet.Shapes("tfc-4-1").Visible = False
                ActiveSheet.Shapes("tfc-777-1").Visible = False
                ActiveSheet.Shapes("tfc-7-1").Visible = False
                ActiveSheet.Shapes("tfc-8-1").Visible = False
                ActiveSheet.Shapes("tfc-9-1").Visible = False
                ActiveSheet.Shapes("tfc-12-1").Visible = False
                ActiveSheet.Shapes("tfc-16-1").Visible = False
                ActiveSheet.Shapes("lpi-1").Visible = False
                ActiveSheet.Shapes("eq-1").Visible = False
                Select Case Target.Value
                    Case "24"
                        ActiveSheet.Shapes("tombstone-1").Visible = True
                        ActiveSheet.Shapes("inputTS").Visible = True
                        ActiveSheet.Shapes("4-2-1").Visible = True
                    Case "PIN"
                        ActiveSheet.Shapes("inputTS").Visible = True
                        ActiveSheet.Shapes("lpi-1").Visible = True
                End Select
        
        End If
    End Sub

  2. #2
    Registered User
    Join Date
    07-06-2011
    Location
    america, usa
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: alternative for (multiple) worksheet change

    I like that idea of setting everything to false only once. I just tried it, however, I need the other shapes to disappear when a new shape is called upon. Unfortunately they stay visible after a new shape appears. How would I start the additional subs? That is what I was trying to accomplish but was unsure of the correct code after additional "worksheet change" subs failed.
    Last edited by eculver; 03-25-2013 at 09:00 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1