ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,596
- Office Version
- 2007
- Platform
- Windows
On my userform i have a command button that will place a value in the comboBox field if its not in the list.
Once done the user will complete the other fields & then use another command button to send all the values to my worksheet.
Im interested to put the two codes together.
It would work like this.
Use command button to add value if not in list.
Complete all the other fields.
Run command button that sends values to worksheet.
MsgBox Was the value in the lisr ?
Yes then run code example 1 below.
No then run code example 2 below.
The userform then closes.
CODE 1
CODE 2
My problem is the following im being asked twice the same Msg question. when using the following & selecting NO to Msg question
Once done the user will complete the other fields & then use another command button to send all the values to my worksheet.
Im interested to put the two codes together.
It would work like this.
Use command button to add value if not in list.
Complete all the other fields.
Run command button that sends values to worksheet.
MsgBox Was the value in the lisr ?
Yes then run code example 1 below.
No then run code example 2 below.
The userform then closes.
CODE 1
VBA Code:
Private Sub SendToWorksheet_Click()
ActiveSheet.ListObjects("Table42").ListRows.Add 1, True
ActiveSheet.ListObjects("Table42").DataBodyRange.RowHeight = 25
ThisWorkbook.Worksheets("QUOTES").Range("D2") = Me.ComboBox1.Text ' VEHICLE
ThisWorkbook.Worksheets("QUOTES").Range("H2") = Me.ComboBox2.Text ' DESCRIPTION OF JOB
ThisWorkbook.Worksheets("QUOTES").Range("K2") = Me.ComboBox3.Text ' PAYMENT
ThisWorkbook.Worksheets("QUOTES").Range("I2") = Me.ComboBox4.Text ' MILEAGE THERE & BACK
ThisWorkbook.Worksheets("QUOTES").Range("A2") = Me.TextBox1.Text ' NAME
ThisWorkbook.Worksheets("QUOTES").Range("B2") = Me.TextBox2.Text ' TELEPHONE
ThisWorkbook.Worksheets("QUOTES").Range("C2") = Me.TextBox3.Text ' POST CODE
ThisWorkbook.Worksheets("QUOTES").Range("E2") = Me.TextBox4.Text ' VEHICLE REG
ThisWorkbook.Worksheets("QUOTES").Range("F2") = Me.TextBox5.Text ' QUOTED
ThisWorkbook.Worksheets("QUOTES").Range("G2") = Me.TextBox6.Text ' DATE OF QUOTE
ThisWorkbook.Worksheets("QUOTES").Range("J2") = Me.TextBox8.Text ' VIN
Unload QuotesForm
Range("A1").Select
ActiveWorkbook.Save
Exit Sub
End Sub
CODE 2
Code:
Private Sub AddNewVehicle_Click()
Dim LastBlankRow As Long
Sheets("INFO").Select
LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
Cells(LastBlankRow, 2).Select
ActiveCell.Value = ComboBox1.Value
Sheets("QUOTES").Select
End Sub
My problem is the following im being asked twice the same Msg question. when using the following & selecting NO to Msg question
Code:
Private Sub SendToWorksheet_Click()
Dim answer As Integer
answer = MsgBox("WAS THE CAR IN THE VEHICLE LIST", vbCritical + vbYesNo + vbDefaultButton2, "ADD NEW CAR MESSAGE")
If answer = vbYes Then
ActiveSheet.ListObjects("Table42").ListRows.Add 1, True
ActiveSheet.ListObjects("Table42").DataBodyRange.RowHeight = 25
ThisWorkbook.Worksheets("QUOTES").Range("D2") = Me.ComboBox1.Text ' VEHICLE
ThisWorkbook.Worksheets("QUOTES").Range("H2") = Me.ComboBox2.Text ' DESCRIPTION OF JOB
ThisWorkbook.Worksheets("QUOTES").Range("K2") = Me.ComboBox3.Text ' PAYMENT
ThisWorkbook.Worksheets("QUOTES").Range("I2") = Me.ComboBox4.Text ' MILEAGE THERE & BACK
ThisWorkbook.Worksheets("QUOTES").Range("A2") = Me.TextBox1.Text ' NAME
ThisWorkbook.Worksheets("QUOTES").Range("B2") = Me.TextBox2.Text ' TELEPHONE
ThisWorkbook.Worksheets("QUOTES").Range("C2") = Me.TextBox3.Text ' POST CODE
ThisWorkbook.Worksheets("QUOTES").Range("E2") = Me.TextBox4.Text ' VEHICLE REG
ThisWorkbook.Worksheets("QUOTES").Range("F2") = Me.TextBox5.Text ' QUOTED
ThisWorkbook.Worksheets("QUOTES").Range("G2") = Me.TextBox6.Text ' DATE OF QUOTE
ThisWorkbook.Worksheets("QUOTES").Range("J2") = Me.TextBox8.Text ' VIN
Unload QuotesForm
Range("A1").Select
ActiveWorkbook.Save
Else
Dim LastBlankRow As Long
Sheets("INFO").Select
LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
Cells(LastBlankRow, 2).Select
ActiveCell.Value = ComboBox1.Value
Sheets("QUOTES").Select
answer = MsgBox("WAS THE CAR IN THE VEHICLE LIST", vbCritical + vbYesNo + vbDefaultButton2, "ADD NEW CAR MESSAGE")
If answer = vbYes Then
ActiveSheet.ListObjects("Table42").ListRows.Add 1, True
ActiveSheet.ListObjects("Table42").DataBodyRange.RowHeight = 25
ThisWorkbook.Worksheets("QUOTES").Range("D2") = Me.ComboBox1.Text ' VEHICLE
ThisWorkbook.Worksheets("QUOTES").Range("H2") = Me.ComboBox2.Text ' DESCRIPTION OF JOB
ThisWorkbook.Worksheets("QUOTES").Range("K2") = Me.ComboBox3.Text ' PAYMENT
ThisWorkbook.Worksheets("QUOTES").Range("I2") = Me.ComboBox4.Text ' MILEAGE THERE & BACK
ThisWorkbook.Worksheets("QUOTES").Range("A2") = Me.TextBox1.Text ' NAME
ThisWorkbook.Worksheets("QUOTES").Range("B2") = Me.TextBox2.Text ' TELEPHONE
ThisWorkbook.Worksheets("QUOTES").Range("C2") = Me.TextBox3.Text ' POST CODE
ThisWorkbook.Worksheets("QUOTES").Range("E2") = Me.TextBox4.Text ' VEHICLE REG
ThisWorkbook.Worksheets("QUOTES").Range("F2") = Me.TextBox5.Text ' QUOTED
ThisWorkbook.Worksheets("QUOTES").Range("G2") = Me.TextBox6.Text ' DATE OF QUOTE
ThisWorkbook.Worksheets("QUOTES").Range("J2") = Me.TextBox8.Text ' VIN
Unload QuotesForm
Range("A1").Select
ActiveWorkbook.Save
End If
End If
Exit Sub
End Sub