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

 

[NdNoviceHlp]

Hmmm... I see Unload QuotesForm but not Quoteform.Show? Anyways, the problem for this error is usually in the initialization and/or activation code for the QuotesForm. Also, this can be problematic...

Range("A1").Select

Be specific...

Sheets("QUOTES").Range("A1").Select

HTH. Dave

 

[ipbr21054]

QuotesForm.Show is the code on commandBUtton on worksheet which opens the userform.
Dont even know why its involved

At present the A1 works fine

 

[NoSparks]

I suspect ComboBox1 isn't finished 'doing its thing' as that is the _BeforeUpdate event.
Try using ComboBox1_AfterUpdate instead.

 

[ipbr21054]

See photo explanation.

1 Complete all the fields apart from adding a new car in Vehicle List field.

I type FERRARI in the Vehicle List & as we know i will get the MsgBox

Now the New car has been added, It is at this point i dont wish to press SEND TO QUOTES DATABASE, so the code on this command button was added to the BEFORE UPDATE button thinking it would work.
But as you guessed it it didnt.

 

[NoSparks]

Re-read post #4.

 

[ipbr21054]

You mean change it to this,If so i get compile error

Private Sub ComboBox1_AfterUpdate(ByVal Cancel As MSForms.ReturnBoolean)

 

[NoSparks]

NO
use the drop downs at the top of the VBA editor

 

[ipbr21054]

Ok,
Didnt know what to do so looking for a pointer.
I now have this code below but still have an issue.

I type the new car "ZIPPY" in vehicle field.
The form closes & i see the details are now added once to the worksheet where before it was twice.
I then get a run time error & when i debug the OPen Form is shown in Yellow.
Do even know why that involved.
Checking the INFO sheet i see the new cae "ZIPPY" is added.

Private Sub ComboBox1_AfterUpdate()
    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
    Sheets("QUOTES").Range("A1").Select
    ActiveWorkbook.Save
End Sub

 

[NoSparks]

Checking the INFO sheet i see the new cae "ZIPPY" is added.

ZIPPY was added by the _BeforeUpdate procedure

Is the QUOTES sheet being populated correctly by this _AfterUpdate procedure ?

added edit:
now see it writes once instead to twice
try moveing the unloading of the form to after the save