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

T

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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Does this error perhaps occur only when you open the workbook?

Do you have code in Sub Workbook_Open that activates this particular worksheet?
 
Upvote 0
Thanks for your reply. It does only happen when I open the workbook and only when the last sheet it was opened on before saving is NOT "Property Numbering".

This is the code from "ThisWorkbook"

VBA Code: 
Private Sub Workbook_Open()
    Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C14,C8").ClearContents
            sh.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            sh.Range("C14,C8").Value = "'Choose"
        ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            sh.Range("C4").Value = "'Choose"
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub
 
Upvote 0
TheJay said:
Thanks for your reply. It does only happen when I open the workbook and only when the last sheet it was opened on before saving is NOT "Property Numbering".

This is the code from "ThisWorkbook"
Click to expand...
I could be wrong but it looks to me that your Workbook open event is making a change to the sheet "Property Numbering" and triggering the Change Event there.
Assuming you don't want to happen try turning off EnableEvents at the start of Workbook open and back on at the end.
Rich (BB code): 
Application.EnableEvents = False
'........ the code ....
Application.EnableEvents = True
 
Upvote 0
Solution
Alex Blakenburg said:
I could be wrong but it looks to me that your Workbook open event is making a change to the sheet "Property Numbering" and triggering the Change Event there.
Assuming you don't want to happen try turning off EnableEvents at the start of Workbook open and back on at the end.
Rich (BB code): 
Application.EnableEvents = False
'........ the code ....
Application.EnableEvents = True
Click to expand...
If I have modified according to your suggestion, it's not fixing the original error:
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)
Application.EnableEvents = False
    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
Application.EnableEvents = True
End Sub
 
Upvote 0
Sorry, in ThisWorkbook:

VBA Code: 
Private Sub Workbook_Open()
Application.EnableEvents = False
    Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C14,C8").ClearContents
            sh.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            sh.Range("C14,C8").Value = "'Choose"
        ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            sh.Range("C4").Value = "'Choose"
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
Application.EnableEvents = True
End Sub
 
Upvote 0
In "Property Numbering":

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
        .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
            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
        End If
    End If
    If Not Intersect(Range("C8"), Target) Is Nothing Then
        If Range("C8").Value = "" Then
            Range("C8").Value = "'Choose"
        End If
    End If
    If Not Intersect(Range("C14"), Target) Is Nothing Then
        If Range("C14").Value = "" Then
            Range("C14").Value = "'Choose"
        End If
    End If
'Dropdown fix
    Range("B2").Select
End Sub
 
Upvote 0
TheJay said:
Sorry, in ThisWorkbook:
Click to expand...
Did putting it in the Workbook_Open event fix the issue ?
ie save it after making the change, close the workbook, reopen the workbook, does it still error out and does the error still go to the same line ?

If not do you know how to set a breakpoint and do you know how to use the immediate window ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Checkbox doesnt show checkmark when it should
Replies
2
Views
86
ipbr21054
I
excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
419
excelbytes
excelbytes
M
  • Question Question
2-Way Cell Referencing
Replies
1
Views
103
jkpieterse
jkpieterse
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
133
Joe4
Joe4
L
  • Locked
  • Question Question
*Updated Question*...on creating a Bi-directional (two-way) Link in Excel that can link multiple rows and columns?? (You all are amazing!)
Replies
2
Views
310
Joe4
Joe4

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

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