Why is selecting a cell in VBA throwing an error and how to fix?

[TheJay]

I have a worksheet called "Property Numbering" within a workbook with multiple sheets.

I think that the problem is if the workbook opens on a different tab, it's throwing this error? Given that the code is contained within the correct worksheet, is it even possible that this is the cause as it shouldn't run when another worksheet is active?

Option Explicit

Private Sub Worksheet_Activate()
'    Call DropDownListToDefault
    Range("B2").Select
    With Worksheets("Property Numbering")
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
'    Dim instructions As Range
    Dim help        As Range
        If Target.Address = "$B$2" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("B7:J9,B12")
            Set example = .Range("B13:J15,B18")
'            Set instructions = .Range("B19:J22")
            Set help = .Range("B24:J24")
'            Union(formatting, example, instructions, help).EntireRow.Hidden = True
            Union(formatting, example, help).EntireRow.Hidden = True
            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
'            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
    End If
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        If Range("B2").Value = "" Then
            Application.EnableEvents = False
            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("C8"), Target) Is Nothing Then
        If Range("C8").Value = "" Then
            Application.EnableEvents = False
            Range("C8").Value = "'Choose"
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("C14"), Target) Is Nothing Then
        If Range("C14").Value = "" Then
            Application.EnableEvents = False
            Range("C14").Value = "'Choose"
            Application.EnableEvents = True
        End If
    End If
'Dropdown fix
    Range("B2").Select
End Sub

Thanks for your help.

 

[TheJay]

It appears to have worked, thank you.

I've not used breakpoints or "the immediate window".

 

[Alex Blakenburg]

If you want to have a look at using a break point this is a 3.5min video which isn't too bad.
VBA: How to Debug Code

For the immediate window, I normally use methods #1 & #4 in this article.
5 Ways to Use the VBA Immediate Window - Excel Campus