Avatar of Animaldrummer04
Animaldrummer04
Flag for United States of America asked on

Excel VBA Run-Time Error "91"

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

Open in new window


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

Open in new window


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

Open in new window


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
Microsoft ExcelVisual Basic.NETVB Script

Avatar of undefined
Last Comment
Animaldrummer04

8/22/2022 - Mon
Martin Liss

I'm surprised that Len(AdjusterForm.Value) = Empty gives you anything useful but in any case AdjusterForm is always going to be un-initialized because you define it in the sub and you don't set it to anything (unless there's code you aren't showing us).
ASKER CERTIFIED SOLUTION
terencino

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
terencino

And if you have commented out that Set statement, then because you have declared the AdjusterForm as a workbook in Dim AdjusterForm As Workbook and then use it in your code Len(AdjusterForm.Value) = Empty then Excel is expecting it to be a workbook but you are treating it like a textbox. Just separate the variable names and it should work better!
...Terry
Norie

To make sure the code is referring to controls on the userform use the Me keyword.
    If Len(Me.AdjusterForm.Value) = Empty And Len(Me.InsuredForm2.Value) > Empty Then

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Animaldrummer04

ASKER
Awesome Terry! I was just confused because in my ContinueButton1 sub I had written the code the same way and it wasn't throwing an error... anyways, I changed the names of the textfields and updated the code for both buttons and now everything that I have so far is working!
Animaldrummer04

ASKER
Thanks for the help, Just another small question. For the textbox validation, I have this code:

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

Open in new window


However, it's not doing what I want (because I think i'm doing it wrong).

Once the user presses "ok", i want the msgbox to go away and leave the form up for the insured to select a file. However, when the user clicks OK the code continues on.

How can I get it to go back to the form to allow the user to select a file before continuing?

This is my code in it's entirety, the textbox validation occurs in ContinueButton1 and ContinueButton2.

Option Explicit

Private Sub UserForm_Initialize()

'ERROR HANDLING.
    On Error GoTo TheEnd
 
'Application.Visible = Not Application.Visible
  
'DISABLE AND HIDE FORM CONTROLS.
    TxtClaimNumber.Enabled = False
    TxtClaimNumber.Visible = False
    ClaimNumberLabel.Visible = False
    TxtAdjusterForm.Enabled = False
    TxtAdjusterForm.Visible = False
    AdjusterFormLabel.Visible = False
    AdjusterFormBrowse.Enabled = False
    AdjusterFormBrowse.Visible = False
    TxtInsuredForm1.Enabled = False
    TxtInsuredForm1.Visible = False
    InsuredFormLabel1.Visible = False
    InsuredFormBrowse1.Enabled = False
    InsuredFormBrowse1.Visible = False
    TxtInsuredForm2.Enabled = False
    TxtInsuredForm2.Visible = False
    InsuredFormLabel2.Visible = False
    InsuredFormBrowse2.Enabled = False
    InsuredFormBrowse2.Visible = False
    ContinueButton1.Enabled = False
    ContinueButton1.Visible = False
    CancelButton1.Enabled = False
    CancelButton1.Visible = False
    ContinueButton2.Enabled = False
    ContinueButton2.Visible = False
    CancelButton2.Enabled = False
    CancelButton2.Visible = False

'ENABLE AND SHOW FORM CONTROLS.
    NewAdjusterForm.Enabled = True
    NewAdjusterForm.Visible = True
    AddToAdjusterForm.Enabled = True
    AddToAdjusterForm.Visible = True
            
TheEnd:
End Sub

Private Sub NewAdjusterForm_Click()

'ENABLE AND SHOW FORM CONTROLS.
    If NewAdjusterForm.Value = True Then
        TxtClaimNumber.Enabled = True
        TxtClaimNumber.Visible = True
        ClaimNumberLabel.Visible = True
        TxtInsuredForm1.Enabled = True
        TxtInsuredForm1.Visible = True
        InsuredFormLabel1.Visible = True
        InsuredFormBrowse1.Enabled = True
        InsuredFormBrowse1.Visible = True
        ContinueButton1.Enabled = True
        ContinueButton1.Visible = True
        CancelButton1.Enabled = True
        CancelButton1.Visible = True
'DISABLE AND HIDE FORM CONTROLS.
        TxtInsuredForm2.Text = ""
        TxtInsuredForm2.Enabled = False
        TxtInsuredForm2.Visible = False
        InsuredFormLabel2.Visible = False
        InsuredFormBrowse2.Enabled = False
        InsuredFormBrowse2.Visible = False
        ContinueButton2.Enabled = False
        ContinueButton2.Visible = False
        CancelButton2.Enabled = False
        CancelButton2.Visible = False
        TxtAdjusterForm.Text = ""
        TxtAdjusterForm.Enabled = False
        TxtAdjusterForm.Visible = False
        AdjusterFormLabel.Visible = False
        AdjusterFormBrowse.Enabled = False
        AdjusterFormBrowse.Visible = False
    End If
        
End Sub

Private Sub InsuredFormBrowse1_Click()
    Dim Filex1 As Variant
    Dim sPath As String
    
'SET INITIAL PATH FOR OPEN FILE DIALOG.
    sPath = "C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory Forms (Optimized)\Excel Version of Insured Form"
    ChDrive sPath
    ChDir sPath

'OPEN FILE DIALOG.
    Filex1 = Application.GetOpenFilename _
        (FileFilter:="Excel Worksheets(*.xls; *.xlsx), *.xls; *.xlsx", _
        Title:="Please select the Insured Form.", MultiSelect:=False)
        
'SHOW SELECTED FILE IN TEXTBOX.
    If Filex1 <> False Then
        TxtInsuredForm1 = Filex1
    End If
    
End Sub

Private Sub AddToAdjusterForm_Click()

'ENABLE AND SHOW FORM CONTROLS.
    If AddToAdjusterForm.Value = True Then
        TxtInsuredForm2.Enabled = True
        TxtInsuredForm2.Visible = True
        InsuredFormLabel2.Visible = True
        InsuredFormBrowse2.Enabled = True
        InsuredFormBrowse2.Visible = True
        ContinueButton2.Enabled = True
        ContinueButton2.Visible = True
        CancelButton2.Enabled = True
        CancelButton2.Visible = True
        TxtAdjusterForm.Enabled = True
        TxtAdjusterForm.Visible = True
        AdjusterFormLabel.Visible = True
        AdjusterFormBrowse.Enabled = True
        AdjusterFormBrowse.Visible = True
'DISABLE AND HIDE FORM CONTROLS.
        TxtClaimNumber.Text = ""
        TxtClaimNumber.Enabled = False
        TxtClaimNumber.Visible = False
        ClaimNumberLabel.Visible = False
        TxtInsuredForm1.Text = ""
        TxtInsuredForm1.Enabled = False
        TxtInsuredForm1.Visible = False
        InsuredFormLabel1.Visible = False
        InsuredFormBrowse1.Enabled = False
        InsuredFormBrowse1.Visible = False
        ContinueButton1.Enabled = False
        ContinueButton1.Visible = False
        CancelButton1.Enabled = False
        CancelButton1.Visible = False
    End If
    
End Sub

Private Sub InsuredFormBrowse2_Click()
    Dim Filex2 As Variant
    Dim sPath As String
    
'SET INITIAL PATH FOR OPEN FILE DIALOG.
    sPath = "C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory Forms (Optimized)\Excel Version of Insured Form"
    ChDrive sPath
    ChDir sPath

'OPEN FILE DIALOG.
    Filex2 = Application.GetOpenFilename _
        (FileFilter:="Excel Worksheets(*.xls; *.xlsx), *.xls; *.xlsx", _
        Title:="Please select the Insured Form.", MultiSelect:=False)
        
'SHOW SELECTED FILE IN TEXTBOX.
    If Filex2 <> False Then
        TxtInsuredForm2 = Filex2
    End If
    
End Sub

Private Sub AdjusterFormBrowse_Click()
    Dim Filex3 As Variant
    Dim sPath As String
    
'SET INITIAL PATH FOR OPEN FILE DIALOG.
    sPath = "C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory Forms (Optimized)\Excel Version of Insured Form"
    ChDrive sPath
    ChDir sPath

'OPEN FILE DIALOG.
    Filex3 = Application.GetOpenFilename _
        (FileFilter:="Excel Worksheets(*.xls; *.xlsx), *.xls; *.xlsx", _
        Title:="Please select the Insured Form.", MultiSelect:=False)
        
'SHOW SELECTED FILE IN TEXTBOX.
    If Filex3 <> False Then
        TxtAdjusterForm = Filex3
    End If

End Sub

Private Sub ContinueButton1_Click()
    Dim MissingClaimNumber As Integer
    Dim MissingInsured As Integer
    Dim MissingBoth As Integer
    Dim InsuredForm As Workbook
    Dim sourceSheet As Worksheet
    Dim AdjusterForm As Workbook
    Dim targetSheet As Worksheet
    Dim ACell As Range
    Dim ActiveCellInTable As Boolean
    Dim CopyFormats As Variant
    Dim NextRow As Long
    Dim sPath As String
    Dim Filex4 As Variant
    Dim FileFormatNum As Long
    Dim mismatch As Integer
    Dim ClaimNumberCheck As Range
    
    
            
'IF THE USER DID NOT ENTER A CLAIM NUMBER
    If Len(TxtClaimNumber.Value) = Empty And Len(TxtInsuredForm1.Value) > Empty Then
        MissingClaimNumber = MsgBox("Please enter the claim number before proceeding.", vbOKOnly, "Claim Number Not Entered!")
    End If

'IF THE USER DID NOT SELECT AN INSURED FORM TO IMPORT
    If Len(TxtClaimNumber.Value) > Empty And Len(TxtInsuredForm1.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 ENTER A CLAIM NUMBER OR SELECT AN INSURED FORM TO IMPORT
    If Len(TxtClaimNumber.Value) = Empty And Len(TxtInsuredForm1.Value) = Empty Then
        MissingBoth = MsgBox("Please select the Insured Form you wish to import and enter the claim number before proceeding", vbOKOnly, "Claim Number Not Entered And Insured Form Not Selected!")
    End If

'IF THE USER ENTERED A CLAIM NUMBER AND SELECTED AN INSURED FORM TO IMPORT
    If Len(TxtClaimNumber.Value) > Empty And Len(TxtInsuredForm1.Value) > Empty Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
'OPEN THE INSURED FORM.
        Set InsuredForm = Application.Workbooks.Open(TxtInsuredForm1.Text)
        Set sourceSheet = InsuredForm.Sheets("Inventory Form")

'COMPARE THE CLAIM NUMBER THE USER ENTERED WITH THE CLAIM NUMBER ENTERED IN THE INSURED FORM.
    Set ClaimNumberCheck = sourceSheet.Range("D5").Find(what:=TxtClaimNumber.Text)
               
        If Len(sourceSheet.Range("D5").Value) > Empty Then
        If ClaimNumberCheck Is Nothing Then
            mismatch = MsgBox("The claim number that you just entered (claim #" & TxtClaimNumber.Text & ") " & _
            "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 the currently selected Insured Form?", _
            vbYesNo, "Claim Number Does Not 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:
'OPEN THE ADJUSTER FORM.
        Set AdjusterForm = Application.Workbooks.Open("C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory " & _
        "Forms (Optimized)\Adjuster Inventory Excel Template\Redesigned 250 item.xltx")
        Set targetSheet = AdjusterForm.Sheets("Inventory")
        
        NextRow = targetSheet.Range("B15:J9999").Find(what:="*", _
        After:=targetSheet.Range("B15"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        SearchFormat:=False).Row + 1
        
'IMPORT THE TOP SECTION WITH THE INSURED'S INFO FROM INSURED FORM TO ADJUSTER FORM.
    targetSheet.Range("L1").Value = sourceSheet.Range("D4").Value
    targetSheet.Range("L2").Value = sourceSheet.Range("D5").Value
    targetSheet.Range("I8").Value = sourceSheet.Range("E7").Value
    targetSheet.Range("G10").Value = sourceSheet.Range("E9").Value
    targetSheet.Range("G12").Value = sourceSheet.Range("E11").Value
      
'SET A REFERENCE TO THE ACTIVECELL ON THE INSURED FORM.
    Set ACell = sourceSheet.Range("B15")

'TEST TO SEE IF ACELL IS IN A TABLE OR LIST. NOTE THAT BY USING ACELL.LISTOBJECT, YOU DO NOT NEED TO
'KNOW THE NAME OF THE TABLE TO WORK WITH IT.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

'IF THE CELL IS IN A LIST OR TABLE, RUN THE CODE.
    If ActiveCellInTable = True Then
                          
'COPY THE VISIBLE CELLS OF TABLE1 "LOCATION" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN B IN TARGETSHEET.
        ACell.ListObject.ListColumns("Location").DataBodyRange.Copy
        With targetSheet.Range("B" & NextRow)
            .PasteSpecial xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False
        End With
       
'COPY THE VISIBLE CELLS OF TABLE1 "QUANTITY" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN C IN TARGETSHEET.
        ACell.ListObject.ListColumns("Quantity").DataBodyRange.Copy
        With targetSheet.Range("C" & NextRow)
            .PasteSpecial xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False
        End With

'COPY THE VISIBLE CELLS OF TABLE1 "DESCRIPTION" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN D IN TARGETSHEET.
            ACell.ListObject.ListColumns("Description").DataBodyRange.Copy
            With targetSheet.Range("D" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With

'COPY THE VISIBLE CELLS OF TABLE1 "PURCHASED" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN G IN TARGETSHEET.
            ACell.ListObject.ListColumns("Purchased").DataBodyRange.Copy
            With targetSheet.Range("G" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'COPY THE VISIBLE CELLS OF TABLE1 "AGE" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN H IN TARGETSHEET.
            ACell.ListObject.ListColumns("Age").DataBodyRange.Copy
            With targetSheet.Range("H" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'COPY THE VISIBLE CELLS OF TABLE1 "COST" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN I IN TARGETSHEET.
            ACell.ListObject.ListColumns("Cost").DataBodyRange.Copy
            With targetSheet.Range("I" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'CLOSE THE INSURED FORM WITHOUT SAVING.
    InsuredForm.Close SaveChanges:=False
       
'SET INITIAL PATH FOR "SAVE AS" FILE DIALOG FOR ADJUSTER FORM.
    sPath = "C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory Forms (Optimized)\Excel Version of Insured Form\"
    ChDrive sPath
    ChDir sPath

'"SAVE AS" FILE DIALOG FOR ADJUSTER FORM.
    With AdjusterForm
         Filex4 = Application.GetSaveAsFilename _
            (InitialFileName:="Adjuster Form " & targetSheet.Range("L2").Value, _
            FileFilter:="Excel WorkBook 2010-2013 (*.xlsx), *.xlsx,", _
            Title:="Please save the new Adjuster Form before proceeding.")
        FileFormatNum = 51
        .SaveAs Filex4, FileFormat:=FileFormatNum, CreateBackup:=False
    End With
    End If
    End If
        
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.ActivateNext
    ActiveWindow.WindowState = xlMinimized
    Application.Visible = True
    Application.Goto targetSheet.Range("L16")
    Unload UserForm1
    ThisWorkbook.Close SaveChanges:=False
    
TheEnd:

End Sub

Private Sub CancelButton1_Click()
Unload UserForm1
ThisWorkbook.Close SaveChanges:=False
End Sub

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 ACell As Range
    Dim ActiveCellInTable As Boolean
    Dim NextRow As Long
    Dim sPath As String
    Dim Filex5 As Variant
    Dim FileFormatNum As Long
    
'IF THE USER DID NOT SELECT AN ADJUSTER FORM TO ADD ITEMS TO
    If Len(TxtAdjusterForm.Value) = Empty And Len(TxtInsuredForm2.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(TxtAdjusterForm.Value) > Empty And Len(TxtInsuredForm2.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(TxtAdjusterForm.Value) = Empty And Len(TxtInsuredForm2.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(TxtAdjusterForm.Value) > Empty And Len(TxtInsuredForm2.Value) > Empty Then
        'Application.ScreenUpdating = False
        'Application.EnableEvents = False
        
'OPEN THE INSURED FORM.
        Set InsuredForm = Application.Workbooks.Open(TxtInsuredForm2.Text)
        Set sourceSheet = InsuredForm.Sheets("Inventory Form")

'OPEN THE ADJUSTER FORM.
        Set AdjusterForm = Application.Workbooks.Open(TxtAdjusterForm.Text)
        Set targetSheet = AdjusterForm.Sheets("Inventory")
        
        NextRow = targetSheet.Range("B15:J9999").Find(what:="*", _
        After:=targetSheet.Range("B15"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        SearchFormat:=False).Row + 1
        
'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:
'IMPORT THE TOP SECTION WITH THE INSURED'S INFO FROM INSURED FORM TO ADJUSTER FORM.
        targetSheet.Range("L1").Value = sourceSheet.Range("D4").Value
        targetSheet.Range("L2").Value = sourceSheet.Range("D5").Value
        targetSheet.Range("I8").Value = sourceSheet.Range("E7").Value
        targetSheet.Range("G10").Value = sourceSheet.Range("E9").Value
        targetSheet.Range("G12").Value = sourceSheet.Range("E11").Value
        
'SET A REFERENCE TO THE ACTIVECELL ON THE INSURED FORM.
        Set ACell = sourceSheet.Range("B15")

'TEST TO SEE IF ACELL IS IN A TABLE OR LIST. NOTE THAT BY USING ACELL.LISTOBJECT, YOU DO NOT NEED TO
'KNOW THE NAME OF THE TABLE TO WORK WITH IT.
        On Error Resume Next
        ActiveCellInTable = (ACell.ListObject.Name <> "")
        On Error GoTo 0

'IF THE CELL IS IN A LIST OR TABLE, RUN THE CODE.
        If ActiveCellInTable = True Then
                          
'COPY THE VISIBLE CELLS OF TABLE1 "LOCATION" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN B IN TARGETSHEET.
            ACell.ListObject.ListColumns("Location").DataBodyRange.Copy
            With targetSheet.Range("B" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
       
'COPY THE VISIBLE CELLS OF TABLE1 "QUANTITY" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN C IN TARGETSHEET.
            ACell.ListObject.ListColumns("Quantity").DataBodyRange.Copy
            With targetSheet.Range("C" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With

'COPY THE VISIBLE CELLS OF TABLE1 "DESCRIPTION" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN D IN TARGETSHEET.
            ACell.ListObject.ListColumns("Description").DataBodyRange.Copy
            With targetSheet.Range("D" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With

'COPY THE VISIBLE CELLS OF TABLE1 "PURCHASED" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN G IN TARGETSHEET.
            ACell.ListObject.ListColumns("Purchased").DataBodyRange.Copy
            With targetSheet.Range("G" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'COPY THE VISIBLE CELLS OF TABLE1 "AGE" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN H IN TARGETSHEET.
            ACell.ListObject.ListColumns("Age").DataBodyRange.Copy
            With targetSheet.Range("H" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'COPY THE VISIBLE CELLS OF TABLE1 "COST" COLUMN IN SOURCESHEET TO THE NEXT AVAILABLE ROW IN COLUMN I IN TARGETSHEET.
            ACell.ListObject.ListColumns("Cost").DataBodyRange.Copy
            With targetSheet.Range("I" & NextRow)
                .PasteSpecial xlPasteValuesAndNumberFormats
                Application.CutCopyMode = False
            End With
            
'CLOSE THE INSURED FORM WITHOUT SAVING.
            InsuredForm.Close SaveChanges:=False
            
'SET INITIAL PATH FOR "SAVE AS" FILE DIALOG FOR ADJUSTER FORM.
            sPath = "C:\Users\Andrew\Desktop\Thumb Drive(05Apr)\Inventory Forms (Optimized)\Excel Version of " & _
            "Insured Form\"
            ChDrive sPath
            ChDir sPath

'"SAVE AS" FILE DIALOG FOR ADJUSTER FORM.
            With AdjusterForm
                Filex5 = Application.GetSaveAsFilename _
                (InitialFileName:="Adjuster Form " & targetSheet.Range("L2").Value, _
                FileFilter:="Excel WorkBook 2010-2013 (*.xlsx), *.xlsx,", _
                Title:="Please save the new Adjuster Form before proceeding.")
                FileFormatNum = 51
                .SaveAs Filex5, FileFormat:=FileFormatNum, CreateBackup:=False
            End With
        End If
    End If
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.ActivateNext
    ActiveWindow.WindowState = xlMinimized
    Application.Visible = True
    Application.Goto targetSheet.Range("L16")
    Unload UserForm1
    ThisWorkbook.Close SaveChanges:=False
    
TheEnd:
End Sub

Private Sub CancelButton2_Click()
Unload UserForm1
ThisWorkbook.Close SaveChanges:=False
End Sub

Private Sub UserForm_Terminate()
    Application.Visible = True
End Sub

Open in new window

terencino

Hi glad it's working better! What about you use an Exit Sub after the message box, like so:
If ...
  msgbox ...
  exit sub
end if

...Terry
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Animaldrummer04

ASKER
Thanks Terry, that works. Can you explain why that wouldn't exit the ContinueButton sub? I thought that was the only sub I was in, and if I put "exit sub" it would take me out of it all.
terencino

But it does exit the ContinueButton1_Click sub ---  it just returns control to whatever called it (in this case the userform which hasn't been changed), unless the event code does something else.
Animaldrummer04

ASKER
ooooh I get it. I keep forgetting it's like a tree hierarchy.

Thanks!
Your help has saved me hundreds of hours of internet surfing.
fblack61