Excel VBA - Why doesn't sheet code recognize my global range variable defined in a module?

Question

Full disclosure, I've done basic programming in a few languages but this is me dipping the toe back in VBA after a long time. I may have missed very basic concepts or syntax...

The purpose of the code is to check a set of cells to make sure they are completed on an excel pseudo form before saving. I define the various cell ranges as public range variables and union various subsets together depending on a check box and radio button.

I want to use the checkbox click sub to set some variables to use for this union and to automatically adjust the conditional format of blank cells that must be completed. That's where I'm running into a problem where the public variables aren't being recognized in the Click Sub. It's probably a simple variable definition error but I'd love an assist on this.

I get a:

Run-Time error '91':

Object variable or With block variable not set

on the Motor call in the checkbox sub. If I explicitly call out the range in one spot, it gives me the same error on the next motor.

Module Code For Defining Variables:

Option Explicit

Public DriveChk As String
Public StageChk As Integer
Public Fixed As Range
Public Motor As Range
Public Engine As Range
Public Stage1 As Range
Public Stage2 As Range
Public Stage3 As Range
Public Required As Range
Public Stage As Range
Public Drive As Range

Code for Workbook

Private Sub Workbook_Open()

'Ensure that the workbook stays protected but that the VBA script has full access to change cells.
ThisWorkbook.Worksheets("Field Service Report").Protect Password:="xxxxx", UserInterfaceOnly:=True

'Message to field service rep
MsgBox "Please complete FSR on the day of service." & vbNewLine & "All fields highlighted in red must be completed in order to save." & vbNewLine & "Please submit completed FSR to xxxxx", vbOK + vbInformation, "REMINDERS"

'Set all of the cell check and formatting ranges
Set Fixed = ThisWorkbook.Worksheets("Field Service Report").Range("E6,Q6,Z6,J7,AB7,J8,D28,O28,D29,A46,A58,F58,L58,P58,U57,V58,Y58")
Set Motor = ThisWorkbook.Worksheets("Field Service Report").Range("Z28,AI28,Z29,AF29,AL29,Z30,AI30,AB31")
Set Engine = ThisWorkbook.Worksheets("Field Service Report").Range("D15,M15,T15,AG15,AL15,T17,AB17,AE17,J23,J24,J25,AB20,AB21,AB22,AB23,AB24,AB25,AL20,AL21,AL22,AL23,AL24,AL25")
Set Stage1 = ThisWorkbook.Worksheets("Field Service Report").Range("I38,O38,AC38,AI38,I39,L39,O39,R39,AC39,AF39,AI39,AL39")
Set Stage2 = ThisWorkbook.Worksheets("Field Service Report").Range("I40,O40,AC40,AI40,I41,L41,O41,R41,AC40,AI40,AC41,F41,AI41,AL41")
Set Stage3 = ThisWorkbook.Worksheets("Field Service Report").Range("I42,O42,AC42,AI42,I43,O43,R43,AC43,AI43,AL43")

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Create the final range of cells for checking
If DriveChk = "G" Then
    Set Drive = Engine
ElseIf DriveChk = "E" Then
    Set Drive = Motor
End If

If StageChk = 1 Then
    Set Stage = Stage1
End If

If StageChk = 2 Then
    Set Stage = Application.Union(Stage1, Stage2)
End If

If StageChk = 3 Then
    Set Stage = Application.Union(Stage1, Stage2, Stage3)
End If

Set Required = Application.Union(Fixed, Drive, Stage)

'Check if all required cells are filled in
If WorksheetFunction.CountA(Required) < Required.Count Then
    Cancel = True
    MsgBox "Please Completed Shaded Cells!", vbOK + vbExclamation, "SAVE CANCELLED"

End If

'Set the report date before saving
Range("AG60") = Format(Now(), "mm-dd-yyyy hh:mm:ss AM/PM")

'Old code probably to ensure saving macro enabled that is currently commented out
'  Dim sFN As String
 '   If SaveAsUI Then
  '      Cancel = True
   '     sFN = Application.GetSaveAsFilename(, _
    '       fileFilter:="Excel Macro-Enabled Workbooks (*.xlsm),*.xlsm")
     '   Debug.Print sFN
      '  If sFN <> "False" Then _
       '     ThisWorkbook.SaveAs sFN, xlOpenXMLWorkbookMacroEnabled
    'End If
End Sub

Sheet Code

Option Explicit

Private Sub CheckBox26_Click()
If CheckBox26.Value = True Then
    'MsgBox text to verify sub is working
    MsgBox "CB26 is true"
    DriveChk = "E"
    Motor.FormatConditions _
        .Delete
    With Motor.FormatConditions _
        .Add(Type:=xlBlanksCondition)
        .Interior.ColorIndex = 30
        .StopIfTrue = True
    End With
Else
    'MsgBox text to verify sub is working
    MsgBox "CB26 is false"
    DriveChk = "Z"
    Motor.FormatConditions _
        .Delete
    With Motor.FormatConditions _
        .Add(Type:=xlBlanksCondition)
        .Interior.ColorIndex = 5
        .StopIfTrue = True
    End With
End If
End Sub

Thanks for the help! Sorry, I don't fully understand the shorthand that will keep long commands from wrapping. I tried to figure it out and my code wouldn't work right.

Yes Motor.FormatConditions.Delete errors. If I substitute the full range definition for Motor it errors with the same error at With Motor.FormatConditions _ — jimm
Also, I originally defined the variables within the workbook but I read that its best practice to put your global variables in a separate module. I got the same error either way. — jimm
Not sure your CF syntax is quite right (though wouldn't give an RTE 91 error), e..g see stackoverflow.com/questions/13661965/… — SJR
I wondered too. It's very hard to find USEFUL examples on here or in Microsoft's documentation. But...I just substituted the full range definition for "Motor" in all the appropriate spots in the checkbox sub and the CF worked exactly as it should. So it's definitely an issue with my defined range variables. — jimm

avb avb · Accepted Answer · 2019-11-13T13:57:28

Your module variables are public but still local to module so should be prepended with module name:

Module1.Motor

Excel VBA - Why doesn't sheet code recognize my global range variable defined in a module?

1 Answers