workbook_sheetchange macro

Question

I have a workbook_sheetchange macro that works off a data validation cell in the tab.

I wanted to have another cell in the same tab do the same thing, but when i copy the macro below over it keeps giving me a duplicate name, so I changed the name and it still doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Target.Address <> "$I$4" Then Exit Sub

    Select Case Target.Text

    Case "Yes"

        Call Unhide_sheets

        Call hidekeepinfo

    Case "No"

        Call FilterKLColumns

        Call unhideKeepers

        Call HideKeepTab

    Case "Start"

        Call UnhideKLcolumns

        Call Unhide_sheets

        Call unhideKeepers

     Case Else

        Exit Sub

    End Select

End Sub

Second macro is as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Range("K7")

  Case "14"

      Case UnFilterKLColumns

  Case "13"

     Case FilterKLColumns

  Case Else 

    Exit Sub

  End Select

End Sub

How do you combine these two macros together so if you trigger I4 cell on dropdown it runs that one.  But if you trigger K7 it runs that one?

Answer 1

As a rule, for most of the change events, (workbook, worksheet, etc) you can only have one instance.  So, only one Private Sub Workbook_SheetChange.  With that said, there is a "cheat".  What I do is create the different events as separate macros, like macro1 and macro 2 and then within the change event, place a call to the separate macros.

An example would look like this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  Call macro1(target)

  Call macro2(target)

End Sub

Sub macro1(ByVal Target As Range)

my code

End Sub

Sub macro2(ByVal Target As Range)

my other code

End Sub

You have to specify if you are watching a target, etc in the different subs.  I am not guaranteeing this to work, but it works for me when I use the multiple ranges to target.

Answer 2

Does anybody have any suggestions on what the code would look like?

Answer 3

Gord

Question what is each cell has different rules or macros to follow?

Answer 4

Maybe this revision fits your needs?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Const WS_RANGE As String = "A3, G6"

If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then

        Select Case Target

            Case "20"

                MsgBox "This is your Message 0 macro!"

            Case "19"

                MsgBox "This is your Message 1 macro!"

            Case "18"

                MsgBox "This is your Message 2 macro!"

            Case "17"

                MsgBox "This is your Message 3 macro!"

            Case "16"

                MsgBox "This is your Message 4 macro!"

            Case "15"

                MsgBox "This is your Message 5 macro!"

            Case "14"

                MsgBox "This is your Message 6 macro!"

        End Select

    End If

End Sub

Gord