AFPathfinder
Well-known Member
- Joined
- Apr 8, 2012
- Messages
- 533
- Office Version
- 365
- 2016
- Platform
- Windows
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):
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:
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.
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):
Code:
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:
Code:
? 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.
Code:
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