Results 1 to 6 of 6

Thread: Private Sub Workbook_SheetChange Not Triggering - why?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Private Sub Workbook_SheetChange Not Triggering - why?

    Why doesn't this work/trigger?

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, _
     ByVal Source As Range)
    
    
    Application.EnableEvents = True
    Application.ScreenUpdating = False
    
    
        If Target.Address <> "$A$1" Then Exit Sub
            Application.Columns("D:BP").Select
            Application.Selection.EntireColumn.Hidden = False
            If Target.Value = "1" Then
                Application.Columns("D:J").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "2" Then
                Application.Columns("D:AM").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "3" Then
                Application.Columns("D:Q").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "4" Then
                Application.Columns("D:X").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "5" Then
                Application.Columns("D:BP").Select
                Application.Selection.EntireColumn.Hidden = False
            End If
            If Target.Value = "6" Then
                Application.Columns("D:AE").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "7" Then
                Application.Columns("D:AS").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "8" Then
                Application.Columns("D:BA").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
            If Target.Value = "9" Then
                Application.Columns("D:BH").Select
                Application.Selection.EntireColumn.Hidden = True
            End If
        End If
    
    
    'Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    
    End Sub

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,644
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Private Sub Workbook_SheetChange Not Triggering - why?

    Do you have it stored in the ThisWorkbook sheet?

    Also, you have "ByVal Source as Range" in the subroutine definition, and you use "Target" in the body of the subroutine. Also, is EnableEvents True before the routine executes? The more usual sequence of events is to disable EnableEvents at the start of the routine, and re-enable it at the end.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Private Sub Workbook_SheetChange Not Triggering - why?

    Every time I had this issue, Application.EnableEvents was set to False (usually after prematurily exiting a macro).

    On a sidenote, I would advise you not to use .Select. The code below would work without having to select anything, which is considered bad practice.

    Code:
    Application.Columns("D:BA").EntireColumn.Hidden
    Also, disable Events before going through the Worksheet_Change event. If the code within the event changes anything on that worksheet, it will trigger itself, resulting in an infinite loop.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Private Sub Workbook_SheetChange Not Triggering - why?

    Try:
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
        Application.EnableEvents = True
        Application.ScreenUpdating = False
        Application.Columns("D:BP").EntireColumn.Hidden = False
        Select Case Target.Value
            Case Is = 1
                Columns("D:J").EntireColumn.Hidden = True
            Case Is = 2
                Columns("D:AM").EntireColumn.Hidden = True
            Case Is = 3
                Columns("D:Q").EntireColumn.Hidden = True
            Case Is = 4
                Columns("D:X").EntireColumn.Hidden = True
            Case Is = 5
                Columns("D:BP").EntireColumn.Hidden = True
            Case Is = 6
                Columns("D:AE").EntireColumn.Hidden = True
            Case Is = 7
                Columns("D:AS").EntireColumn.Hidden = True
            Case Is = 8
                Columns("D:BA").EntireColumn.Hidden = True
            Case Is = 9
                Columns("D:BH").EntireColumn.Hidden = True
            End Select
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    New Member
    Join Date
    Sep 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Private Sub Workbook_SheetChange Not Triggering - why?

    This is why I pay you guys the big bucks!

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Private Sub Workbook_SheetChange Not Triggering - why?

    Glad we could help.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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