Two values on userform but only send one value to worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
I have a userform which has values in Comboboxes & Textboxes.
For this post the concern is TextBox7 & TextBox9
These represent quotes if i go to customer & if customer comes to me.
So at the time of the quote both are shown on the userform.

So now is the time the customer has advised if he is coming to me or im going to him.
So using the code shown below the values from my form are sent to the worksheet.

BUT
I need something that will ask the question.
Depending on the answer depends if TextBox7 or TextBox9 gets transfered from userform to worksheet.

This code sends values from userform to worksheet.

VBA Code:
Private Sub CommandButton1_Click()
    
  With ThisWorkbook.Worksheets("DATABASE") ' THIS CODE BELOW WILL TRANSFER DATABASE VALUES TO WORKSHEET
 
    ThisWorkbook.Worksheets("DATABASE").Range("B6") = Me.ComboBox1.Text ' REGISTRATION NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("C6") = Me.ComboBox2.Text ' BLANK USED
    ThisWorkbook.Worksheets("DATABASE").Range("D6") = Me.ComboBox3.Text ' VEHICLE
    ThisWorkbook.Worksheets("DATABASE").Range("E6") = Me.ComboBox4.Text ' BUTTONS
    ThisWorkbook.Worksheets("DATABASE").Range("F6") = Me.ComboBox5.Text ' ITEM SUPPLIED
    ThisWorkbook.Worksheets("DATABASE").Range("G6") = Me.ComboBox6.Text ' TRANSPONDER CHIP
    ThisWorkbook.Worksheets("DATABASE").Range("H6") = Me.ComboBox7.Text ' JOB ACTION
    ThisWorkbook.Worksheets("DATABASE").Range("I6") = Me.ComboBox8.Text ' PROGRAMMER USED
    ThisWorkbook.Worksheets("DATABASE").Range("J6") = Me.ComboBox9.Text ' KEY CODE
    ThisWorkbook.Worksheets("DATABASE").Range("K6") = Me.ComboBox10.Text ' BITING
    ThisWorkbook.Worksheets("DATABASE").Range("L6") = Me.ComboBox11.Text ' CHASIS NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("N6") = Me.ComboBox12.Text ' VEHICLE YEAR
    ThisWorkbook.Worksheets("DATABASE").Range("R6") = Me.TextBox1.Text ' ADDRESS 1st LINE
    ThisWorkbook.Worksheets("DATABASE").Range("S6") = Me.TextBox2.Text ' ADDRESS 2nd LINE
    ThisWorkbook.Worksheets("DATABASE").Range("T6") = Me.TextBox3.Text ' ADDRESS 3rd LINE
    ThisWorkbook.Worksheets("DATABASE").Range("U6") = Me.TextBox4.Text ' ADDRESS 4TH LINE
    ThisWorkbook.Worksheets("DATABASE").Range("V6") = Me.TextBox5.Text ' POST CODE
    ThisWorkbook.Worksheets("DATABASE").Range("W6") = Me.TextBox6.Text ' CONTACT NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("O6") = Me.TextBox7.Text ' QUOTED / PAID
    ThisWorkbook.Worksheets("DATABASE").Range("AD6") = Me.TextBox8.Text ' MILEAGE THERE & BACK
    ThisWorkbook.Worksheets("DATABASE").Range("L6") = Me.ComboBox11.Text ' CHASSIS NUMBER
    ThisWorkbook.Worksheets("DATABASE").Range("AA6") = Me.ComboBox13.Text ' SUPPLIER
    ThisWorkbook.Worksheets("DATABASE").Range("AC6") = Me.ComboBox14.Text ' PAYMENT TYPE
    ThisWorkbook.Worksheets("DATABASE").Range("AB6") = Me.ComboBox15.Text ' PART NUMBER
  
    End With
       Unload DatabaseToSheet2
       ActiveWorkbook.Sheets("QUOTES").Activate
       Range("A2").Select
       ActiveCell.EntireRow.Delete
       Range("A1").Select
       ActiveWorkbook.Save

End Sub

TheTextBox value in question on the userform will be sent to worksheet at Row O
Im not sure then how to cancel out the other Textbox from being sent

Thanks
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
BUT
I need something that will ask the question.
Depending on the answer depends if TextBox7 or TextBox9 gets transfered from userform to worksheet.

Check if the following is what you need.
Answer the question Yes for Textbox7, No for Textbox9

VBA Code:
Private Sub CommandButton1_Click()
  Dim answ As VbMsgBoxResult
  Dim txt As String
    
  answ = MsgBox("Press Yes for Textbox7, No for Textbox9 or Cancel to abort procedure", vbYesNoCancel)
  
  Select Case answ
    Case vbYes:    txt = TextBox7
    Case vbNo:     txt = TextBox9
    Case vbCancel: Exit Sub
  End Select
  
  With ThisWorkbook.Worksheets("DATABASE") ' THIS CODE BELOW WILL TRANSFER DATABASE VALUES TO WORKSHEET
 
    .Range("B6") = Me.ComboBox1.Text ' REGISTRATION NUMBER
    .Range("C6") = Me.ComboBox2.Text ' BLANK USED
    .Range("D6") = Me.ComboBox3.Text ' VEHICLE
    .Range("E6") = Me.ComboBox4.Text ' BUTTONS
    .Range("F6") = Me.ComboBox5.Text ' ITEM SUPPLIED
    .Range("G6") = Me.ComboBox6.Text ' TRANSPONDER CHIP
    .Range("H6") = Me.ComboBox7.Text ' JOB ACTION
    .Range("I6") = Me.ComboBox8.Text ' PROGRAMMER USED
    .Range("J6") = Me.ComboBox9.Text ' KEY CODE
    .Range("K6") = Me.ComboBox10.Text ' BITING
    .Range("L6") = Me.ComboBox11.Text ' CHASIS NUMBER
    .Range("N6") = Me.ComboBox12.Text ' VEHICLE YEAR
    .Range("R6") = Me.TextBox1.Text ' ADDRESS 1st LINE
    .Range("S6") = Me.TextBox2.Text ' ADDRESS 2nd LINE
    .Range("T6") = Me.TextBox3.Text ' ADDRESS 3rd LINE
    .Range("U6") = Me.TextBox4.Text ' ADDRESS 4TH LINE
    .Range("V6") = Me.TextBox5.Text ' POST CODE
    .Range("W6") = Me.TextBox6.Text ' CONTACT NUMBER
    .Range("O6") = txt ' QUOTED / PAID
    .Range("AD6") = Me.TextBox8.Text ' MILEAGE THERE & BACK
    .Range("L6") = Me.ComboBox11.Text ' CHASSIS NUMBER
    .Range("AA6") = Me.ComboBox13.Text ' SUPPLIER
    .Range("AC6") = Me.ComboBox14.Text ' PAYMENT TYPE
    .Range("AB6") = Me.ComboBox15.Text ' PART NUMBER

  End With
    
  Unload DatabaseToSheet2
  ActiveWorkbook.Sheets("QUOTES").Activate
  Range("A2").Select
  ActiveCell.EntireRow.Delete
  Range("A1").Select
  ActiveWorkbook.Save
End Sub
 
Upvote 0
Solution
That works great.Can you show me how i add a title to Msgbox as currently for title i see the number 3

This is what i have at present
VBA Code:
answ = MsgBox("PRESS YES IF GOING THERE, NO IF COMING HERE or CANCEL TO ABORT THIS", vbInformation, vbYesNoCancel)
 
Upvote 0
Sorry this is the correct sytax in use.

VBA Code:
 answ = MsgBox("PRESS YES IF GOING THERE, NO IF COMING HERE or CANCEL TO ABORT THIS", vbYesNoCancel)

Looking to add Title & also the ding sound as if vbInformation was used.

This msgbox code is different to what i use normally
 
Upvote 0
Looking to add Title & also the ding sound as if vbInformation was used.

Try:

VBA Code:
  Beep
  answ = MsgBox("PRESS YES IF GOING THERE, NO IF COMING HERE or CANCEL TO ABORT THIS", vbYesNoCancel, "SELECT THE DESIRED OPTION")
 
Upvote 0

Forum statistics

Threads
1,224,862
Messages
6,181,458
Members
453,042
Latest member
AbdelrahmanExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top