Correct sytax for yes / no Msgbox

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,738
Office Version
  1. 2007
Platform
  1. 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
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
I had found my mistake & now corrected it & shown below.

The issue for me now is that the code works the wrong way around.

If i select YES to the Msgbox question it should then RUN THE BLUE CODE but IT RUNS THE red CODE ?

Rich (BB code): 
Private Sub SendToWorksheet_Click()
        
      Dim answer As Integer
         answer = MsgBox("DID YOU MANUALLY ADD NEW CAR TO VEHICLE LIST ?", vbInformation + vbYesNo + vbDefaultButton2, "ADD NEW CAR MESSAGE")
         
      If answer = vbYes Then ' SEND DATABASE VALUES TO WORKSHEET
         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 'ADD NEW CAR TO DATABASE VEHICLE ON INFO SHEET THEN CONTINUE TO SEND SEND DATABASE VALUES TO WORKSHEET
         Sheets("INFO").Select
         LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
         Cells(LastBlankRow, 2).Select
         ActiveCell.Value = ComboBox1.Value
         Sheets("QUOTES").Select
         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
      Exit Sub
End Sub
 
Upvote 0
OK so changing the vbYes to vbNo works fine.

But why is it doing the opposite to what it should be ?
 
Upvote 0
Your second "If answer = vbYes" is included in branch "Else" (i.e. when answer=vbNo).
 
Upvote 0
Try something like this:
Excel Formula: 
Private Sub CommandButton1_Click()
Dim answer As Integer
answer = MsgBox("Do You want to clear this row", vbYesNo)
If answer = vbYes Then
MsgBox "Yes"
End If
If answer = vbNo Then
MsgBox "No"
End If
End Sub
 
Upvote 0
To be honest I thought I had used that but when I’m first asked the question & I selected No it then run the opposite code to what it should have.

Basically.
Did you manually enter the new car in the Combobox field.
The only difference in the two codes are.
Add to INFO sheet first then send userform values to worksheet.
OR
Just send userform values straight to worksheet as the value was already in the list.
In the post above I selected Yes because I had entered a value in the Combobox field.
So I’m expecting the value to be added to INFO sheet then come back to other sheet with userform on & then send values to worksheet.

It didn’t do that.
It just sent values userform to worksheet.

So the car I typed isn’t in the INFO sheet column B which is where it should have been
 
Upvote 0
I just showed you an example.
Not sure about all your other code.

But you will see I did not use an Else after this
 
Upvote 0
Solution
ipbr21054 said:
If i select YES to the Msgbox question it should then RUN THE BLUE CODE but IT RUNS THE red CODE ?
Click to expand...

If that is the case, then why not just change vbYes to vbNo as you talked about above? That will provide you with the required action (selecting "Yes" runs the blue code).
VBA Code: 
Private Sub SendToWorksheet_Click()

    Dim answer As Integer
    answer = MsgBox("DID YOU MANUALLY ADD NEW CAR TO VEHICLE LIST ?", vbInformation + vbYesNo + vbDefaultButton2, "ADD NEW CAR MESSAGE")

    If answer = vbNo Then                             ' SEND DATABASE VALUES TO WORKSHEET
        '
        ' *** This is the RED code ******
        '
        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 RED code ******
        '
    Else        'else means answer = vbYes
        '
        ' *** Thia is the BLUE code ******
        '
        Dim LastBlankRow As Long                      'ADD NEW CAR TO DATABASE VEHICLE ON INFO SHEET THEN CONTINUE TO SEND SEND DATABASE VALUES TO WORKSHEET
        Sheets("INFO").Select
        LastBlankRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
        Cells(LastBlankRow, 2).Select
        ActiveCell.Value = ComboBox1.Value
        Sheets("QUOTES").Select
        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 BLUE code ******
        '
    End If
    Exit Sub
End Sub
 
Upvote 0
I also think it would be easier for you when writing code to us "With"
This keeps you from having to keep entering sheet names or whatever
Here is just an example.
.
Excel Formula: 
Sub RectangleSingleCornerSnipped1_Click()
'Using With"
With Sheets("Alpha")
    .Range("A1").Value = "Me"
    .Range("A2").Value = "You"
End With

End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Last row in table resizes after new row inserted at top
Replies
3
Views
209
ipbr21054
I
I
  • Solved
DTPicker1 value to worksheet advice
Replies
2
Views
172
ipbr21054
I
I
  • Solved
Two values on userform but only send one value to worksheet
Replies
5
Views
247
ipbr21054
I
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
430
NoSparks
N
I
  • Question Question
Delete row on another sheet from a userform
2 3
Replies
22
Views
744
ipbr21054
I

Forum statistics

Threads
1,223,912
Messages
6,175,344
Members
452,638
Latest member
Oluwabukunmi

Share this page

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