Greetings all, long time viewer first time poster. Been teaching myself VBA for the last month.
I have a userform with some dropdowns that populate from a worksheet. When the user clicks Go, if the first dropdown matches a specific string ("Failed Attack") then a second userform is launched. When the user clicks Go on the second form, I want control to return to the sub in the first form. This was working for me yesterday, but I added some code (not much, nothing that I can remember that would cause the error) and now the following happens:
When the second userform is launched, and the user clicks OK on it, I get an Error 91 (Object variable or with block variable not set). Clicking debug highlights the show command for the second userform. This is confusing to me, because I assume that since the second userform is displayed, the code should go to the next line.
Below is the code for when you click Go on the first userform (called ufCloseWizard):
Private Sub bGo_Click()
Dim i As Long
CloseReason = Me.cbReason.Value ' set reason
CloseClient = Me.cbClient.Value ' set client
CloseAlert = Me.cbAlert.Value ' set alert
If CloseReason = "Failed Attack" Then ufFailedAttack.Show ' Show failed attack box for more information if needed ****THIS IS WHERE ERROR 91 OCCURS AFTER CLICKING GO ON UFFAILEDATTACK, DEBUG HIGHLIGHTS ufFailedAttack.Show
Unload Me ' close the form
CloseCommentStartRow = 0 ' reset starting row
i = 2 ' start looking for the starting row at row 2
With Workbooks(CLOSEMASTERWB).Worksheets(CLOSEMASTERWS) ' look in the master document
Do While .Cells(i, 1) <> "" ' keep looking until we find a blank row
If .Cells(i, 1).Value = CloseReason Then ' if the reason matches
If .Cells(i, 2).Value = CloseClient Then ' and the client
If .Cells(i, 3).Value = CloseAlert Then ' and the alert
CloseCommentStartRow = i ' thats our row
Exit Do ' jump out of the loop
End If
End If
End If
If CloseCommentStartRow = 0 Then i = i + 1 ' if we haven't found our row yet, keep looking
Loop
End With
If CloseCommentStartRow = 0 Then ' if we still havent found our row, error out
MsgBox "Error finding the proper row for comments - please contact SOC_Helper support personnel for assistance"
Exit Sub
End If
Select Case CloseReason ' look at the reason and call the proper sub
Case "Scan"
Call Close_Scan
Case "Failed Attack"
Call Close_Failed
Case Else
MsgBox "Method of closing events (" & CloseReason & ") is currently not supported. Look for it in a future update!"
End Select
If IsWorkbookOpen(CLOSEMASTERWB) Then Workbooks(CLOSEMASTERWB).Close ' close the master file when done
End Sub
This is the code for when the user clicks Go on the second userform (ufFailedAttack):
Private Sub bGoFA_Click()
CloseFailedReason = Me.cbReasonFA.Value ' set Failed reason
CloseSpecialNote = Me.tbSpecial.Value ' set special note
CloseCriteria1 = Me.cbCriteria1.Value ' set criteria
CloseCriteria2 = Me.cbCriteria2.Value
CloseCriteria3 = Me.cbCriteria3.Value
Unload Me
End Sub
All the Close* variables are publically defined in a regular module, and CLOSEMASTERWB and CLOSEMASTERWS are defined as public constants in a regular module.
This is the code when the second userform is loaded (in case its relevant):
Private Sub UserForm_Initialize() <<< Changing this to Activate solved the problem. See below reply.
Me.cbReasonFA.AddItem "Certian O/Ss Immune"
Me.cbReasonFA.AddItem "Certain Devices Immune"
Me.cbReasonFA.AddItem "Special Note in DB"
With Me ' prepare the box
.StartUpPosition = 0 ' clear out any startup spot to keep it from hopping around
.Left = Application.Left + 0.5 * Application.Width - 0.5 * .Width
.Top = Application.Top + 0.5 * Application.Height - 0.5 * .Height
.tbSpecial.Visible = False ' hide these cause we dont need them yet
.cbCriteria1.Visible = False
.cbCriteria2.Visible = False
.cbCriteria3.Visible = False
.lSpecial.Visible = False ' hide these cause we dont need them yet
.lCriteria1.Visible = False
.lCriteria2.Visible = False
.lCriteria3.Visible = False
.bGoFA.Enabled = False
.Show ' show the box
End With
End Sub
Thanks in advance for any clues to this mystery! The data I'm working with is confidential so I cannot post the actual worksheets, but I'll be here for another 7 hours and am more than happy to try any suggestions.
Bookmarks