Excel VBA: WorkSheet_SelectionChange Not Running

[bt_24]

Hi Everyone,

The issue I am having is that the Worksheet_SelectionChange is not firing for me. I have a bunch of worksheets hidden in the file to decrease clutter, with a Navigation page visible - where clicking on the cell with the tile would unhide a corresponding sheet. For example cell B3 with text of "MLB" it would unhide the MLB worksheet and so on for the other pages.

In my workbook I have one module where I set Public variables for the current workbook and all of the worksheets in the workbook. Below is the code that I have in the specific navigation worksheet. All of the other sheets would be currently hidden. Can someone please advise how to get this code to run or point me in the right direction? I have made sure in the immediate window that screen updating and enabling events was set to true.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   
   
    Set Document = ActiveWorkbook
    
    Set SummarySheet = Sheets("Navigation")
    Set MLB = Sheets("MLB General")
    Set NBA = Sheets("NBA General")
    Set NFL = Sheets("NFL General")
    
        
      'if statement for if cell is selected make the sheet visible
        If Target.Address = SummarySheet.Range("B3") Then
            MLB.Visible = True
            
            MLB.Activate
            MLB.Range("A1").Select
                   
            ElseIf Target.Address = SummarySheet.Range("B4") Then
                NBA.Visible = True
            
                NBA.Activate
                NBA.Range("A1").Select
        
            ElseIf Target.Address = SummarySheet.Range("B5") Then
                NFL.Visible = True
            
                NFL.Activate
                NFL.Range("A1").Select
                      
            Else
                'do nothing
        End If
        
   
End Sub

 

[korhan]

Under which worksheet module you are keeping this code?

 

[HackSlash]

Let's make sure you are in the correct place and force Excel to re-write the declaration for you.

1. Looking at the Navigation page, right click the Navigation tab
2. Select "View Code"
3. In the first dropdown above the code window select "Worksheet"
4. The the second dropdown above the code window select "SelectionChange"
   (This should create a new sub in the correct location. )
5. Copy the code from your old sub in to this new sub.
6. Delete the old sub.

Now, let me suggest that you use "BeforeDoubleClick" instead because it's just better than single click. SelectionChange event will fire all the time, even when using arrow keys or code to select... DoubleClick will only capture the mouse.

 

[Bill Rockenbach]

Your code is probably running but not unhiding the workbook because your code is wrong. You must compare the target address to the cell address. If you put a msgbox at the top of your code you can tell if it is firing. Here is the correct tested code with some code added to hide the sheets before unhiding the desired sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

MsgBox "Fired"
   
    Set Document = ActiveWorkbook
    
    Set SummarySheet = Sheets("Navigation")
    Set MLB = Sheets("MLB General")
    Set NBA = Sheets("NBA General")
    Set NFL = Sheets("NFL General")
    
    MLB.Visible = False
    NBA.Visible = False
    NFL.Visible = False
    
        
      'if statement for if cell is selected make the sheet visible
        If Target.Address = SummarySheet.Range("B3").Address Then
            MLB.Visible = True
            
            MLB.Activate
            MLB.Range("A1").Select
                   
            ElseIf Target.Address = SummarySheet.Range("B4").Address Then
                NBA.Visible = True
            
                NBA.Activate
                NBA.Range("A1").Select
        
            ElseIf Target.Address = SummarySheet.Range("B5").Address Then
                NFL.Visible = True
            
                NFL.Activate
                NFL.Range("A1").Select
                      
            Else
                'do nothing
        End If
        
   
End Sub

 

[Bill Rockenbach]

Note: Since the Address is a string you could compare it to a string. For example

ElseIf Target.Address = "$B$4" Then

 

[bt_24]

Hi @korhan appreciate the quick response, the code is in the sheet not a module

 

[bt_24]

Hi @HackSlash I followed you steps but the code continues erroring out. I think because the code is currently placed in the proper sheet. I appreciate the suggestion of adding the rule for double clicking and will heed your advice after getting the by selection to work.

 

[bt_24]

@Bill Rockenbach thank you so much for clarifying the issue with comparing the target address to the cell address. Your code worked perfectly and adding in the .Address to the end of the range was the solution! I really appreciate the help and insight.