Receive Automation error when running code & values added to sheet twice

[ipbr21054]

On my userform i have a piece of code that adds a value to a table if that value wasnt present.
After the value has been aded i then need to click the command button to send the userform values to worksheet.
What i am trying to do is once i select YES on the Msgbox to DO YOU WISH TO ADD IT then using a code on another command button just then send userform values to worksheet with me having to do it.
The process works but i then see the Automation error & when i debug i see QUOTESFORM.Show in Yellow.
I also see that the code has sent values from userform to worksheet twice.


This code works fine,but i then need to select the command button to send values to worksheet.

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim response As Integer
    Dim oNewRow As ListRow

' if combo empty stop here
If Len(Me.ComboBox1.Value) = 0 Then Exit Sub

' is entry in drop down
If Not Me.ComboBox1.MatchFound Then
    ' ask if to add entry to list
    response = MsgBox("VEHICLE ISNT IN VEHICLE LIST" & vbCrLf & vbCrLf & "DO YOU WISH TO ADD IT ?", vbYesNo + vbCritical, "ADD VEHICLE TO LIST")
    
    If response = vbYes Then
        
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        ' add row to table
        With Sheets("INFO").ListObjects("Table2")
            Set oNewRow = .ListRows.Add
            ' put what was entered into list
            oNewRow.Range.Cells(1) = Me.ComboBox1.Value
            '** SORT A=Z HERE **
            .Sort.SortFields.Clear
            .Sort.SortFields.Add KEY:=.ListColumns(1).Range, SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With .Sort
                .Header = xlYes
                .Apply
            End With
            Application.Goto (.HeaderRowRange.Cells(1))
        End With
                
        ' re-activate QUOTES sheet
        Sheets("QUOTES").Select
        
        ' assign the new list to combo
        With Me.ComboBox1

            .List = Sheets("INFO").ListObjects("Table2").DataBodyRange.Value
        End With
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
  
    Else    ' response was NOT vbYes
       MsgBox Me.ComboBox1.Value & vbCrLf & "THE VEHICLE SHOWN ABOVE" & vbCrLf & "WILL NOT BE ADDED TO VEHICLE LIST", vbInformation, "VEHICLE WONT BE ADDED TO LIST MESSAGE"
        ' clear entry
        Me.ComboBox1.Value = ""
        ' maintain focus
        Cancel = True
        Exit Sub
    End If
    
End If

End Sub

This is the code for userform to worksheet transfer.

Private Sub SendToWorksheet_Click()
      
    Application.ScreenUpdating = False

    With Sheets("QUOTES")
       .ListObjects("Table42").ListRows.Add 1, True
       .ListObjects("Table42").DataBodyRange.RowHeight = 25
       .Range("D2") = Me.ComboBox1.Text ' VEHICLE
       .Range("H2") = Me.ComboBox2.Text ' DESCRIPTION OF JOB
       .Range("K2") = Me.ComboBox3.Text ' PAYMENT
       .Range("I2") = Me.ComboBox4.Text ' MILEAGE THERE & BACK
       .Range("A2") = Me.TextBox1.Text ' NAME
       .Range("B2") = Me.TextBox2.Text ' TELEPHONE
       .Range("C2") = Me.TextBox3.Text ' POST CODE
       .Range("E2") = Me.TextBox4.Text ' VEHICLE REG
       .Range("F2") = Me.TextBox5.Text ' QUOTED
       .Range("G2") = Me.TextBox6.Text ' DATE OF QUOTE
       .Range("J2") = Me.TextBox8.Text ' VIN
    End With
    
    Application.ScreenUpdating = True
    
    Unload QuotesForm
    Range("A1").Select
    ActiveWorkbook.Save
      
End Sub

Please see screenshot showing where i added the code.

Thanks

 

[ipbr21054]

Yes check it out here
I took a screen shot when i completed the form then compaired it to whats on the sheet

 

[NoSparks]

Did you see the edit to my last post ?

If that doesn't fix anything I'd suggest just keep using the button.

 

[ipbr21054]

It does the same

 

[NoSparks]

Just use the button, I can't see it being a big deal.

If you're using the mouse to select an existing vehicle from the drop down,
then you've already got the mouse in your hand and can quickly click the button.

If you've typed in a new vehicle you have to hit enter, the msg to add pops up, hit enter again and, if your tab stop order is set right, focus moves directly to the button and you hit enter again. As quick and easy as 1-2-3.