ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,596
- Office Version
- 2007
- Platform
- Windows
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.
This is the code for userform to worksheet transfer.
Please see screenshot showing where i added the code.
Thanks
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.
VBA Code:
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.
Code:
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