IF statement problems

[jose001]

Hi Guys,

Been struggling with this one for quite a while and could really do with some help. I’m trying to nestle a message box within an IF statement, is that possible? Either I’m missing some End if’s or I’ve just got the whole structure wrong, either way I can’t progress past this point. Can anyone provide some pointers as to where I’m going wrong?


At the moment I have something like this.....

IF *(checks if any fields are empty) Then
MsgBox "Your Form is not ready for submission. Please complete ALL fields before continuing.", vbOKOnly + vbExclamation, "ERROR"

Else

Answer = MsgBox("Please review the information below and ensure that everything is correct. If there are any errors, press No to correct the form." & vbCrLf & _
"Your name:" & TextBox1.Value & 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
Etc etc

Unload Me
UserForm2.Show

If Answer = vbNo Then

UserForm1.Show

End If

 

[Comfy]

Replaced you If Answer = vbNo with Else

and added an End IF

IF *(checks if any fields are empty) Then 
MsgBox "Your Form is not ready for submission. Please complete ALL fields before continuing.", vbOKOnly + vbExclamation, "ERROR"

Else

Answer = MsgBox("Please review the information below and ensure that everything is correct. If there are any errors, press No to correct the form." & vbCrLf & _
"Your name:" & TextBox1.Value & 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
Etc etc

Unload Me
UserForm2.Show

Else

UserForm1.Show
End if
End If

 

[jose001]

Wow superfast reply Thanks Comfy. I'll check it out and let you know if I manage to get it working. Many thanks again!

 

[jose001]

Hi Comfy,

I've tried the ammendments but it's now de-bugging at the first IF statement any ideas?

 

[Comfy]

Nope.

Can you post the rest of your code.

 

[jose001]

Here's the lot...

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 ComboBox4.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 & _
        "XXXXX: " & ComboBox1.Value & vbCrLf & _
        " XXXXX: " & TextBox1.Value & vbCrLf & _
        " XXXXX: " & TextBox2.Value & vbCrLf & _
        " XXXXX: " & ComboBox2.Value & vbCrLf & _
        " XXXXX: " & TextBox3.Value & vbCrLf & _
        " XXXXX: " & TextBox4.Value & vbCrLf & vbCrLf & _
        " XXXXX.: " & TextBox5.Value & vbCrLf & _
        " XXXXX: " & ComboBox3.Value & vbCrLf & _
        " XXXXX: " & TextBox7.Value & vbCrLf & _
        " XXXXX: " & TextBox6.Value & vbCrLf & vbCrLf & _
        " XXXXX: " & TextBox8.Value & vbCrLf & _
        " XXXXX: " & TextBox9.Value & vbCrLf & _
        " XXXXX: " & TextBox10.Value & vbCrLf & _
        " XXXXX: " & 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

Else

UserForm1.Show

End If
End If


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


End Sub

 

[Comfy]

and what is the error that it throws?

Do you have a comboBox on your form called ComboBox4?

 

[jose001]

Uh-oh Egg on face!!! Sorry Comfy, just realised that I changed a textbox for a ComboBox and didn't bother to update everything, of dear!!! I'll update things now and hopefully it'll work. I'll let you know. Thanks again