Problems with MsgBox

[jose001]

Hello Everyone,

As you can tell from the code below which I've cobbled together, I'm a bit of an amateur! I've made a form in vba and have used an 'if' as a way to error check it for empty fields. There's almost certainly a proper way to do this but it's my little 'doesn't exactly work' work around. Nevertheless, I'm now encountering problems going back to the form from the MsgBox. Can anyone give me any pointers. If the error isn't glaringly obvious then I can provide more on what I'm trying to do

Many Thanks in advance....






Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim i As Long
i = ActiveSheet.UsedRange.Rows.Count + 1
LastRow = i


If ComboBox1.Value = "" Or ComboBox2.Value = "" Or ComboBox3.Value = "" Or TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Or TextBox6.Value = "" Or TextBox7.Value = "" Or TextBox8.Value = "" Or TextBox9.Value = "" Then
MsgBox "Your form is not ready for submission. Please complete ALL fields before continuing.", vbOKOnly + vbExclamation, "ERROR"


Else

MsgBox "Please review your form below and ensure that all of the information is correct. If there are any errors, press No to correct the form." & vbCrLf & vbCrLf & vbCrLf & _
"XXX: " & ComboBox1.Value & vbCrLf & _
"XXX: " & TextBox1.Value & vbCrLf & _
"XXX: " & TextBox2.Value & vbCrLf & _
"XXX: " & ComboBox2.Value & vbCrLf & _
"XXX: " & TextBox3.Value & vbCrLf & _
"XXX: " & TextBox4.Value & vbCrLf & vbCrLf & _
"XXX.: " & TextBox5.Value & vbCrLf & _
"XXX: " & ComboBox3.Value & vbCrLf & _
"XXX: " & TextBox7.Value & vbCrLf & _
"XXX: " & TextBox6.Value & vbCrLf & vbCrLf & _
"XXX: " & TextBox8.Value & vbCrLf & _
"XXX: " & TextBox9.Value & vbCrLf & _
"XXX: " & TextBox10.Value & vbCrLf & _
"XXX: " & TextBox11.Value & vbCrLf & vbCrLf & _
"Would you like to submit your form?", vbYesNo

If vbNo Then

UserForm1.Show

Else

Sheets("Data").Visible = True
Sheets("Data").Select

i = ActiveSheet.UsedRange.Rows.Count + 1
LastRow = i

Range("A" & i).Value = Date
Range("B" & i).Value = TextBox2.Value
Range("C" & i).Value = ComboBox2.Value
Range("D" & i).Value = TextBox3.Value
Range("E" & i).Value = TextBox4.Value
Range("F" & i).Value = ComboBox1.Value
Range("G" & i).Value = TextBox1.Value
Range("H" & i).Value = TextBox5.Value
Range("I" & i).Value = TextBox7.Value
Range("J" & i).Value = ComboBox3.Value
Range("K" & i).Value = TextBox6.Value
Range("L" & i).Value = TextBox8.Value
Range("M" & i).Value = TextBox9.Value
Range("N" & i).Value = TextBox10.Value
Range("O" & i).Value = TextBox11.Value

Unload Me
UserForm2.Show

End If


Sheets("Data").Visible = False
Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

You need to assign the result of MsgBox to a variable. Example:

Sub Test()
    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Continue?", vbYesNo)
    If Answer = vbNo Then
        MsgBox "You clicked No"
    End If
End Sub

Note that the MsgBox arguments must be in parentheses.

 

[jose001]

Hi Andrew,

Thanks for your help. I've tried changing my code as you've suggested but I still have no idea where I'm going wrong Any ideas?


Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim Answer As VbMsgBoxResult
Dim i As Long
i = ActiveSheet.UsedRange.Rows.Count + 1
LastRow = i


If ComboBox1.Value = "" Or ComboBox2.Value = "" Or ComboBox3.Value = "" Or TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Or TextBox6.Value = "" Or TextBox7.Value = "" Or TextBox8.Value = "" Or TextBox9.Value = "" Then
MsgBox "Your form is not ready for submission. Please complete ALL fields before continuing.", vbOKOnly + vbExclamation, "ERROR"


Else

Answer = MsgBox("Please review your form below and ensure that all of the information is correct. If there are any errors, press No to correct the form." & vbCrLf & vbCrLf & vbCrLf & _
"XXX: " & ComboBox1.Value & vbCrLf & _
"XXX: " & TextBox1.Value & vbCrLf & _
"XXX: " & TextBox2.Value & vbCrLf & _
"XXX: " & ComboBox2.Value & vbCrLf & _
"XXX: " & TextBox3.Value & vbCrLf & _
"XXX: " & TextBox4.Value & vbCrLf & vbCrLf & _
"XXX.: " & TextBox5.Value & vbCrLf & _
"XXX: " & ComboBox3.Value & vbCrLf & _
"XXX: " & TextBox7.Value & vbCrLf & _
"XXX: " & TextBox6.Value & vbCrLf & vbCrLf & _
"XXX: " & TextBox8.Value & vbCrLf & _
"XXX: " & TextBox9.Value & vbCrLf & _
"XXX: " & TextBox10.Value & vbCrLf & _
"XXX: " & TextBox11.Value & vbCrLf & vbCrLf & _
"Would you like to submit your form?", vbYesNo)

If Answer = vbYes Then

Sheets("Data").Visible = True
Sheets("Data").Select

i = ActiveSheet.UsedRange.Rows.Count + 1
LastRow = i

Range("A" & i).Value = Date
Range("B" & i).Value = TextBox2.Value
Range("C" & i).Value = ComboBox2.Value
Range("D" & i).Value = TextBox3.Value
Range("E" & i).Value = TextBox4.Value
Range("F" & i).Value = ComboBox1.Value
Range("G" & i).Value = TextBox1.Value
Range("H" & i).Value = TextBox5.Value
Range("I" & i).Value = TextBox7.Value
Range("J" & i).Value = ComboBox3.Value
Range("K" & i).Value = TextBox6.Value
Range("L" & i).Value = TextBox8.Value
Range("M" & i).Value = TextBox9.Value
Range("N" & i).Value = TextBox10.Value
Range("O" & i).Value = TextBox11.Value

Unload Me
UserForm2.Show

End If

If Answer = vbNo Then

Userform1.Show

End If


Sheets("Data").Visible = False
Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

What's the problem?

When posting code please use CODE tags:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[jose001]

End sub is highlighted and I get the message:

Compile error:
Block if Without End if

 

[Andrew Poulsom]

Add another End If after the last one.