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

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
1xACZUB.png

8OfXCUK.png


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?

VBA Code:
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.
 
It appears to have worked, thank you.

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top