troubleshooting Question

Excel VBA Run-Time Error "91"

Avatar of Animaldrummer04
Animaldrummer04Flag for United States of America asked on
Microsoft ExcelVisual Basic.NETVB Script
10 Comments1 Solution1642 ViewsLast Modified:
Ok, this one is really befuddling me.

What i have here is a section of script for my continue button that is going to open two speadsheets and compare a value in a specific cell in each sheet. If they match, then eventually the code is going to continue. If not, a vbOk dialog box needs to pop up alerting that the numbers don't match, and the sub ends.

Up until this point, it worked. All the dialog boxes popped up when the conditions were met, and if all conditions were met it opened the InsuredForm spreadsheet:

Private Sub ContinueButton2_Click()
    Dim MissingAdjuster As Integer
    Dim MissingInsured As Integer
    Dim MissingBoth As Integer
    Dim InsuredForm As Workbook
    Dim sourceSheet As Worksheet
    Dim ClaimNumberCheck As Range
    Dim AdjusterForm As Workbook
    Dim targetSheet As Worksheet
    Dim mismatch As Integer
    Dim LookingGood As Integer
    
'IF THE USER DID NOT SELECT AN ADJUSTER FORM TO ADD ITEMS TO
    If Len(AdjusterForm.Value) = Empty And Len(InsuredForm2.Value) > Empty Then
        MissingAdjuster = MsgBox("Please select the Adjuster Form you wish to add items to " & _
        "before proceeding.", vbOKOnly, "Adjuster Form Not Selected!")
    End If

'IF THE USER DID NOT SELECT AN INSURED FORM TO IMPORT
    If Len(AdjusterForm.Value) > Empty And Len(InsuredForm2.Value) = Empty Then
        MissingInsured = MsgBox("Please select the Insured Form you wish to import before proceeding.", _
        vbOKOnly, "Insured Form Not Selected!")
    End If

'IF THE USER DID NOT SELECT AN INSURED FORM TO IMPORT OR AN ADJUSTER FORM TO ADD ITEMS TO
    If Len(AdjusterForm.Value) = Empty And Len(InsuredForm2.Value) = Empty Then
        MissingBoth = MsgBox("Please select the Insured Form you wish to import and the Adjuster Form " & _
        "you wish to add items to before proceeding.", vbOKOnly, "Claim Number Not Entered And Insured Form " & _
        "Not Selected!")
    End If

'IF THE USER SELECTED AN INSURED FORM TO IMPORT AND AN ADJUSTER FORM TO ADD ITEMS TO
    If Len(AdjusterForm.Value) > Empty And Len(InsuredForm2.Value) > Empty Then
        'Application.ScreenUpdating = False
        'Application.EnableEvents = False
        
'OPEN THE INSURED FORM.
        Set InsuredForm = Application.Workbooks.Open(InsuredForm2.Text)
        Set sourceSheet = InsuredForm.Sheets("Inventory Form")
        End If
End Sub

ScreenUpdating and EnableEvents are commented out because I'm still building the script.

The problem is, I added all the rest of this code before testing again, and now I get the "Run-Time Error "91" Object variable or With block variable not set" error on this line (that worked just fine before I added the additional code):

If Len(AdjusterForm.Value) = Empty And Len(InsuredForm2.Value) > Empty Then

Additional code I added (between opening the Insured Form and "End If" to make that line not work:

'OPEN THE ADJUSTER FORM.
        Set AdjusterForm = Application.Workbooks.Open(AdjusterForm.Text)
        Set targetSheet = AdjusterForm.Sheets("Inventory")
        
'COMPARE THE CLAIM NUMBER THE USER ENTERED WITH THE CLAIM NUMBER ENTERED IN THE INSURED FORM.
        Set ClaimNumberCheck = sourceSheet.Range("D5").Find(what:=targetSheet.Range("L2"))
        If Len(sourceSheet.Range("D5").Value) > Empty Then
        If ClaimNumberCheck Is Nothing Then
            mismatch = MsgBox("The claim number inside the Adjuster Form (Claim #" & targetSheet.Range("L2") & ") " & _
            "and the claim number that is inside the Insured Form (Claim #" & sourceSheet.Range("D5").Value & ") " & _
            "do not match." & vbNewLine & vbNewLine & _
            "Do you want to proceed with adding the items in the selected Insured Form to the selected Adjuster Form?", _
            vbYesNo, "Claim Numbers Don't Match!")
            If mismatch = vbNo Then
                'Application.ScreenUpdating = True
                Application.Goto sourceSheet.Range("D5")
                sourceSheet.Range("D5").Interior.Color = RGB(250, 250, 0)
                Unload UserForm1
                'Application.Visible = True
                GoTo TheEnd
            Else
                'GoTo ContinueImporting
            End If
        Else
            GoTo ContinueImporting
        End If
        End If

ContinueImporting:
        LookingGood = MsgBox("Looking good so far!", vbOKOnly, "Great Job!")
        End If
End Sub

The thing I can't wrap my head around is that now, even when I comment out (or completely remove) the code that was added to give me the error, I still keep getting the error. I have the exact same validation technique for ContinueButton1, only instead of comparing two cell values it's checking a textbox on the userform compared to a cell value in a workbook it opens. That whole button works fine.

What's going on here?

I've attached the userform for evaluation. I took out the second part of the code, not sure if it's going to work for you guys (because it originally worked for me before I added the additional script).
Master-UserForm.xlsm
ASKER CERTIFIED SOLUTION
terencino

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros