vba - In Excel, updating a formula seems to trigger an unrelated private sub on the same worksheet. How can I get this to stop?

Question

I've created a drop down menu through data validation for workbook navigation. The following is a snippet of code I have for the drop down box to change worksheets in the workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
    If Not (Application.Intersect(Range("J4"), Target) Is Nothing) Then _
        ThisWorkbook.Sheets("Home").Visible = False
        ThisWorkbook.Sheets(Target.Value).Activate
        ThisWorkbook.Sheets(Target.Value).Visible = True
        ThisWorkbook.Sheets("Imported Data").Visible = False
End Sub

The code is meant to hide all other worksheets that are accessible by the drop down list besides the one selected. I have about 10 tabs and this code has worked perfectly to achieve the basic goal of navigation. However, some pages have formulas and when you update data in the cells meant for calculations the workbook jumps to a random worksheet in the workbook that is not at all referenced in this sub.

Is there some way to have my worksheets not try to do anything with this sub unless the dropdown menu itself is changed?

Bonus (less important) Question: is there a way to make the drop box default to (blank) unless the menu itself is accessed?

Answer 1

Then _

1. The space followed by an underscore _ means that the current statement isn't finished yet but continues on the next line. Right now the last 3 lines will run whenever there is a change in the worksheet. Put the entire code in If-Endif.

2. Also avoid unnecessary use of On Error Resume Next. Use proper error handling.

3. You need to make the sheet visible before you activate it and not vice versa.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    
    If Not (Application.Intersect(Range("J4"), Target) Is Nothing) Then
        ThisWorkbook.Sheets("Home").Visible = False
        
        ThisWorkbook.Sheets(Target.Value).Visible = True
        ThisWorkbook.Sheets(Target.Value).Activate
        
        ThisWorkbook.Sheets("Imported Data").Visible = False
    End If
    
Letscontinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

is there a way to make the drop box default to (blank) unless the menu itself is accessed?

If you have created it with Data Valdation then insert a blank value in the list.