VBA: Global Variables Unloading After Userform Close

[AFPathfinder]

Good day, Excel Guru's!

Right to the point: After closing a userform and opening a userform from the same project (addin file) referencing global object variables, I get the RTE 91 "Object variable or With block variable not set".

I normally don't use global variables, but this project is very large, code-wise, and uses the same worksheet objects over and over. I have to reference the workbook and sheet in each reference to the tables on the addin sheets, so I decided to load the object variables on Workbook_Open event (public declarations are in a separate module):

Option Explicit

'Public variables for the workbook sheet objects within the addin.
Public shtConfig As Worksheet           'Config
Public shtTemplate114 As Worksheet      'DD 114
Public shtSort As Worksheet             'Tbl_Trans_SortList
Public shtField As Worksheet            'Tbl_Field_Data
Public shtRank As Worksheet             'Tbl_Ranks
Public shtADSN As Worksheet             'Tbl_ADSN
Public shtCountry As Worksheet          'Tbl_Country_Codes
Public shtCurr As Worksheet             'Tbl_Currency_Codes
Public shtLocation As Worksheet         'Tbl_Location_Codes
Public shtState As Worksheet            'Tbl_States
Public shtCert As Worksheet             'Tbl_Certifiers
Public shtNCycle As Worksheet           'Tbl_Current_Cycle
Public shtOCycle As Worksheet           'Tbl_Old_Cycles


Private Sub Workbook_Open()
    'Runs when the workbook is opened.
    
    'Set workbook sheet objects in memory.
    Set shtConfig = ThisWorkbook.Sheets("Config")
    Set shtTemplate114 = ThisWorkbook.Sheets("DD 114")
    Set shtSort = ThisWorkbook.Sheets("Tbl_Trans_SortList")
    Set shtField = ThisWorkbook.Sheets("Tbl_Field_Data")
    Set shtRank = ThisWorkbook.Sheets("Tbl_Ranks")
    Set shtADSN = ThisWorkbook.Sheets("Tbl_ADSN")
    Set shtCountry = ThisWorkbook.Sheets("Tbl_Country_Codes")
    Set shtCurr = ThisWorkbook.Sheets("Tbl_Currency_Codes")
    Set shtLocation = ThisWorkbook.Sheets("Tbl_Location_Codes")
    Set shtState = ThisWorkbook.Sheets("Tbl_States")
    Set shtCert = ThisWorkbook.Sheets("Tbl_Certifiers")
    Set shtNCycle = ThisWorkbook.Sheets("Tbl_Current_Cycle")
    Set shtOCycle = ThisWorkbook.Sheets("Tbl_Old_Cycles")
End Sub

I checked the objects using the immediate window and the intellisense was picking up that the object was a worksheet, but I got the same error with this command:

? shtConfig.Name

Any thoughts?

For completeness, here is the code behind one of the smaller forms that generates the error. The error occurs on the first usage of a sheet object variable on the Userform_Initialize event.

Option Explicit

Private Sub btnCertNew_Click()
    'On Save Changes command button click.
    
    Dim intRow As Integer               'Holds the listbox row number of the selected certifier.
    
    'To stop listbox click event from executing code. Not a great solution.
    lblCatch.Caption = "R"
    
    'Identify the next row number.  ListCount is offset by one from the ListIndex.
    intRow = shtCert.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    'Fill the new row with the data from the input fields.
    shtCert.Range("A" & intRow).Value = cboRank.Value & " " & txtFName.Value & " " & txtLName.Value
    shtCert.Range("B" & intRow).Value = txtFName.Value
    shtCert.Range("C" & intRow).Value = txtMInit.Value
    shtCert.Range("D" & intRow).Value = txtLName.Value
    shtCert.Range("E" & intRow).Value = txtSuffx.Value
    shtCert.Range("F" & intRow).Value = cboRank.Value
    shtCert.Range("G" & intRow).Value = txtSigPreview.Value
    
    'Clear the input fields.
    txtFName.Value = ""
    txtMInit.Value = ""
    txtLName.Value = ""
    txtSuffx.Value = ""
    cboRank.ListIndex = 0
    txtSigPreview.Value = ""
    btnSaveChanges.Enabled = False
    btnCertNew.Enabled = False
    
    'Reset the rowsource for the listbox.
    lstCertifiers.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" & Range("A3:G" & intRow).Address
    lstCertifiers.ListIndex = -1
    
    'To resume listbox click event effect.
    lblCatch.Caption = ""
End Sub
Private Sub btnDelete_Click()
    'On Delete Selected button press.
    
    Dim intRow As Integer               'Holds the listbox row number of the selected certifier.
    
    'Variable the row integer of the selected item.
    intRow = lstCertifiers.ListIndex + 3
    
    'If a record is selected in the cycle listbox, remove the record.
    shtCert.Range("A" & intRow).EntireRow.Delete
    
    'Reset the rowsource for the listbox.
    If lstCertifiers.ListCount > 0 Then
        lstCertifiers.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" & Range("A3:G" _
            & shtCert.Cells(Rows.Count, 1).End(xlUp).Row).Address
        lstCertifiers.ListIndex = -1
    End If
    
    'Clear the input fields.
    txtFName.Value = ""
    txtMInit.Value = ""
    txtLName.Value = ""
    txtSuffx.Value = ""
    cboRank.ListIndex = 0
    txtSigPreview.Value = ""
    btnSaveChanges.Enabled = False
    btnCertNew.Enabled = False
    lstCertifiers.ListIndex = -1
End Sub
Private Sub btnSaveChanges_Click()
    'On Save Changes command button click.
    
    Dim intRow As Integer               'Holds the listbox row number of the selected certifier.
    
    'To stop listbox click event from executing code. Not a great solution.
    lblCatch.Caption = "R"
    
    'Variable the row integer of the selected item.
    intRow = lstCertifiers.ListIndex + 3
    
    'Change the selected item's values.
    shtCert.Range("A" & intRow).Value = cboRank.Value & " " & txtFName.Value & " " & txtLName.Value
    shtCert.Range("B" & intRow).Value = txtFName.Value
    shtCert.Range("C" & intRow).Value = txtMInit.Value
    shtCert.Range("D" & intRow).Value = txtLName.Value
    shtCert.Range("E" & intRow).Value = txtSuffx.Value
    shtCert.Range("F" & intRow).Value = cboRank.Value
    shtCert.Range("G" & intRow).Value = txtSigPreview.Value
    
    'Clear the input fields.
    txtFName.Value = ""
    txtMInit.Value = ""
    txtLName.Value = ""
    txtSuffx.Value = ""
    cboRank.ListIndex = 0
    txtSigPreview.Value = ""
    btnSaveChanges.Enabled = False
    btnCertNew.Enabled = False
    lstCertifiers.ListIndex = -1
    
    'To resume listbox click event effect.
    lblCatch.Caption = ""
End Sub
Private Sub cboRank_Change()
    'On combobox value change.
    
    'Update the signature preview textbox.
    txtSigPreview = CertifierSig(txtFName.Value, txtMInit.Value, txtLName.Value, txtSuffx.Value, cboRank.Value)
    
    'Enable save and add buttons if first name, last name, and rank have a value.
    If txtFName.Value <> "" And txtLName.Value <> "" And cboRank.Value <> "" Then
        If lstCertifiers.ListIndex > -1 Then
            btnSaveChanges.Enabled = True
        End If
        btnCertNew.Enabled = True
    Else
        btnSaveChanges.Enabled = False
        btnCertNew.Enabled = False
    End If
End Sub
Private Sub lstCertifiers_Click()
    'On listbox click event.

    'If the listbox has a selected value, add the row data to the Certifier Information frame.
    If lstCertifiers.ListIndex > -1 And lblCatch.Caption <> "R" Then
        txtFName = lstCertifiers.List(, 1)
        txtMInit = lstCertifiers.List(, 2)
        txtLName = lstCertifiers.List(, 3)
        txtSuffx = lstCertifiers.List(, 4)
        cboRank = lstCertifiers.List(, 5)
        txtSigPreview = lstCertifiers.List(, 6)
        btnDelete.Enabled = True
    Else
        btnDelete.Enabled = False
    End If
End Sub
Private Sub txtFName_Change()
    'On combobox value change.
    
    'Update the signature preview textbox.
    txtSigPreview = CertifierSig(txtFName.Value, txtMInit.Value, txtLName.Value, txtSuffx.Value, cboRank.Value)
    
    'Enable save and add buttons if first name, last name, and rank have a value.
    If txtFName.Value <> "" And txtLName.Value <> "" And cboRank.Value <> "" Then
        If lstCertifiers.ListIndex >= -1 Then
            btnSaveChanges.Enabled = True
        End If
        btnCertNew.Enabled = True
    Else
        btnSaveChanges.Enabled = False
        btnCertNew.Enabled = False
    End If
End Sub
Private Sub txtLName_Change()
    'On combobox value change.
    
    'Update the signature preview textbox.
    txtSigPreview = CertifierSig(txtFName.Value, txtMInit.Value, txtLName.Value, txtSuffx.Value, cboRank.Value)
    
    'Enable save and add buttons if first name, last name, and rank have a value.
    If txtFName.Value <> "" And txtLName.Value <> "" And cboRank.Value <> "" Then
        If lstCertifiers.ListIndex > -1 Then
            btnSaveChanges.Enabled = True
        End If
        btnCertNew.Enabled = True
    Else
        btnSaveChanges.Enabled = False
        btnCertNew.Enabled = False
    End If
End Sub
Private Sub txtMInit_Change()
    'On combobox value change.
    
    'Update the signature preview textbox.
    txtSigPreview.Value = CertifierSig(txtFName.Value, txtMInit.Value, txtLName.Value, txtSuffx.Value, cboRank.Value)
    
    'Enable save and add buttons if first name, last name, and rank have a value.
    If txtFName.Value <> "" And txtLName.Value <> "" And cboRank.Value <> "" Then
        If lstCertifiers.ListIndex > -1 Then
            btnSaveChanges.Enabled = True
        End If
        btnCertNew.Enabled = True
    Else
        btnSaveChanges.Enabled = False
        btnCertNew.Enabled = False
    End If
End Sub
Private Sub txtSuffx_Change()
    'On combobox value change.
    
    'Update the signature preview textbox.
    txtSigPreview = CertifierSig(txtFName.Value, txtMInit.Value, txtLName.Value, txtSuffx.Value, cboRank.Value)
    
    'Enable save and add buttons if first name, last name, and rank have a value.
    If txtFName.Value <> "" And txtLName.Value <> "" And cboRank.Value <> "" Then
        If lstCertifiers.ListIndex > -1 Then
            btnSaveChanges.Enabled = True
        End If
        btnCertNew.Enabled = True
    Else
        btnSaveChanges.Enabled = False
        btnCertNew.Enabled = False
    End If
End Sub
Private Sub UserForm_Initialize()
    'Executes before userform is shown to the user.

    'Position in the middle of the same screen the button is clicked.
    With Certifiers
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
    
    'Load rowsource to listbox if there are any certifiers listed.
    If shtCert.Cells(Rows.Count, 1).End(xlUp).Row > 2 Then
        lstCertifiers.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" _
            & Range("A3:G" & shtCert.Cells(Rows.Count, 1).End(xlUp).Row).Address
    End If
    
    'Load rowsource to comboboxes.
    cboCertDefault.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" _
        & Range("A2:A" & shtCert.Cells(Rows.Count, 1).End(xlUp).Row).Address
    cboRank.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Ranks'!" _
        & Range("A2:A" & shtRank.Cells(Rows.Count, 1).End(xlUp).Row).Address
        
    'Set default combobox values.
    cboCertDefault.Value = shtConfig.Range("B" & RowFind("Config", "Default Certifier")).Value
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'When userform exits.
    
    'Add default certifier combobox value to the config worksheet.
    shtConfig.Range("B" & RowFind("Config", _
        "Default Certifier")).Value = cboCertDefault.Value
    'Exit userform
    Unload Me
End Sub

 

[hotabae]

If they aren't already, put these at the top of a general module (i.e. - module1) rather than inside the ThisWorkbook code.

'Public variables for the workbook sheet objects within the addin.
Public shtConfig As Worksheet           'Config
Public shtTemplate114 As Worksheet      'DD 114
Public shtSort As Worksheet             'Tbl_Trans_SortList
Public shtField As Worksheet            'Tbl_Field_Data
Public shtRank As Worksheet             'Tbl_Ranks
Public shtADSN As Worksheet             'Tbl_ADSN
Public shtCountry As Worksheet          'Tbl_Country_Codes
Public shtCurr As Worksheet             'Tbl_Currency_Codes
Public shtLocation As Worksheet         'Tbl_Location_Codes
Public shtState As Worksheet            'Tbl_States
Public shtCert As Worksheet             'Tbl_Certifiers
Public shtNCycle As Worksheet           'Tbl_Current_Cycle
Public shtOCycle As Worksheet           'Tbl_Old_Cycles

Leave the Workbook_Open() stuff where it is though.

Then lemme know how it goes.

Edit: Oops, just read "public declarations are in a separate module" -- my bad

 

[hotabae]

I threw together a test workbook for this and I am not having issues accessing the global variable.

ThisWorkbook Code:

Private Sub Workbook_Open()
Set testSheet = ThisWorkbook.Sheets("Sheet1")
End Sub

Module1 Code (called by a command button on Sheet1):

Public testSheet As Worksheet
Sub openForm()
UserForm1.Show
End Sub

UserForm1 Code (has 1 label):

Public Sub UserForm_Initialize()
Me.Label1.Caption = testSheet.Name
End Sub

I'm not coming up with any great ideas about what is wrong. Short of a spelling error somewhere, maybe it has something to do with how you call the userform/ where you call it from?

Otherwise, I hope someone else can chime in on the issue cause I'm beat.

 

[AFPathfinder]

Actually, the problem seems to be intermittent. Might this be caused when a run is ended in break mode? I had a control reference that was incorrect and I was ending midway through a method.

The Userform_QueryClose event doesn't contain any code that would impact the value of those global variables. The sturdiness of the tool is a big concern of mine as it will be sent to users who don't have local VBA support. Would it be advisable to put the object assignment into a method, which is called at the close event of all userforms (or initialization of all userforms)?

 

[hotabae]

So what I'm noticing in my test workbook:

​Sometimes... If I trigger the _QueryClose Event (clicking the x in the corner or w/e)

I cannot re-initialize the form.

I think the "Unload Me" within the _QueryClose Event is your culprit. - Though I don't understand why it is resetting your globals.

 

[hotabae]

Scratch that, I'm getting the error whenever I "stop" the code after a break on the userForm, regardless of whether or not the _QueryClose code is active.

Seems strange, but I think you are right about it having to do with the break mode.

The stop button is the same as clicking "End" on the debug menu.

There's some info here (have to wait for the solutions)

https://www.experts-exchange.com/questions/28313246/global-variables-cleared-after-ending-debug.html

 

[dmt32]

Hi,
perhaps change how & when you intialize your variables

Place in a STANDARD module

'Public variables for the workbook sheet objects within the addin.
Public shtConfig As Worksheet           'Config
Public shtTemplate114 As Worksheet      'DD 114
Public shtSort As Worksheet             'Tbl_Trans_SortList
Public shtField As Worksheet            'Tbl_Field_Data
Public shtRank As Worksheet             'Tbl_Ranks
Public shtADSN As Worksheet             'Tbl_ADSN
Public shtCountry As Worksheet          'Tbl_Country_Codes
Public shtCurr As Worksheet             'Tbl_Currency_Codes
Public shtLocation As Worksheet         'Tbl_Location_Codes
Public shtState As Worksheet            'Tbl_States
Public shtCert As Worksheet             'Tbl_Certifiers
Public shtNCycle As Worksheet           'Tbl_Current_Cycle
Public shtOCycle As Worksheet           'Tbl_Old_Cycles




Sub IntializeVariables(ByVal State As Integer)


'Set workbook sheet objects in memory (xlOn) or release from memory (xlOff).
  With ThisWorkbook
    Set shtConfig = IIf(State = xlOn, .Sheets("Config"), Nothing)
    Set shtTemplate114 = IIf(State = xlOn, .Sheets("DD 114"), Nothing)
    Set shtSort = IIf(State = xlOn, .Sheets("Tbl_Trans_SortList"), Nothing)
    Set shtField = IIf(State = xlOn, .Sheets("Tbl_Field_Data"), Nothing)
    Set shtRank = IIf(State = xlOn, .Sheets("Tbl_Ranks"), Nothing)
    Set shtADSN = IIf(State = xlOn, .Sheets("Tbl_ADSN"), Nothing)
    Set shtCountry = IIf(State = xlOn, .Sheets("Tbl_Country_Codes"), Nothing)
    Set shtCurr = IIf(State = xlOn, .Sheets("Tbl_Currency_Codes"), Nothing)
    Set shtLocation = IIf(State = xlOn, .Sheets("Tbl_Location_Codes"), Nothing)
    Set shtState = IIf(State = xlOn, .Sheets("Tbl_States"), Nothing)
    Set shtCert = IIf(State = xlOn, .Sheets("Tbl_Certifiers"), Nothing)
    Set shtNCycle = IIf(State = xlOn, .Sheets("Tbl_Current_Cycle"), Nothing)
    Set shtOCycle = IIf(State = xlOn, .Sheets("Tbl_Old_Cycles"), Nothing)
  End With


End Sub

In your UserForms

Private Sub UserForm_Initialize()
 'Executes before userform is shown to the user.
    IntializeVariables xlOn
    'Position in the middle of the same screen the button is clicked.
    With Certifiers
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With

    'Load rowsource to listbox if there are any certifiers listed.
    If shtCert.Cells(Rows.Count, 1).End(xlUp).Row > 2 Then
        lstCertifiers.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" _
            & Range("A3:G" & shtCert.Cells(Rows.Count, 1).End(xlUp).Row).Address
    End If

    'Load rowsource to comboboxes.
    cboCertDefault.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Certifiers'!" _
        & Range("A2:A" & shtCert.Cells(Rows.Count, 1).End(xlUp).Row).Address
    cboRank.RowSource = "'[" & ThisWorkbook.Name & "]Tbl_Ranks'!" _
        & Range("A2:A" & shtRank.Cells(Rows.Count, 1).End(xlUp).Row).Address

    'Set default combobox values.
    cboCertDefault.Value = shtConfig.Range("B" & RowFind("Config", "Default Certifier")).Value
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'When userform exits.

    'Add default certifier combobox value to the config worksheet.
    shtConfig.Range("B" & RowFind("Config", _
        "Default Certifier")).Value = cboCertDefault.Value


 'Exit userform
    IntializeVariables xlOff
End Sub

Suggestion should ensure variables are intialized when form opened & released from memory when closed.

you should not need Unload Me in the QueryClose event

Dave

 

[RoryA]

If you end code from break mode, you have a state loss and all your public variables will be reset.

 

[AFPathfinder]

Thank you hotabae, dmt32, and RoryA! That all makes sense and I believe I'll try dmt32's suggestion. That should help alleviate a future state loss with those variables. Again, thank you all!

Also, thanks for the tip on "Unload Me", dmt32.