vba forms & messages

[cchamill]

Hi i hope someone can help me. i have created a form with around six fields for entry. Once these are completed you click a button and it updates one of the excel sheets & generates a message to say please complete further boxes (that are then visible to you on the excel page) if the person tries to click the button to continue without having entered data in all boxes or if the data in one particular box does not match a set criteria an error message comes up and takes them back to the form.

The problem i have is that if either of these 2 error message boxes has been triggered the message about completing the excel fields comes up an additional time for each error triggered. so if you have triggered error box twice you get 3 messages to continue instead of just the once. I guess it is looping round somewhere but have no idea how to fix it

i have shown my code below (apologies if it is a bit long - i am new to vba!)

thanks

Private Sub cmdeurope_Click()
With projectquote
If .txtname = "" Or .txtdate = "" Or .cboname = "" Or .Cbotype = "" Or .cbocountry = "" Or .cbocurrency = "" Then
MsgBox ("Not all boxes completed")
End If
If .cbocountry <> "Europe Only" Then
MsgBox ("Selected Option does not match country code")
projectquote.Hide
projectquote.Show
End If
Sheets("europe").Select
Range("E4") = .txtname
Range("E6") = .txtdate
Range("E8") = .cboname
Range("E10") = .Cbotype
Range("E12") = .cbocountry
Range("E14") = .cbocurrency
End With
projectquote.Hide
Sheets("europe").Select
answer = MsgBox("Please complete Project Duration & Complexity Boxes for European Resource")
Sheets("europe").Select
Range("b17").Select
End Sub

 

[Kenneth Hobson]

Use Exit Sub after your MsgBox.

 

[cchamill]

just changed it and works fine - thanks!

 

[davedarave]

Hi,

I have got this code for my button in my form but it is telling me I have an error ever since I have put in the code for the message box. Could you please advise?

Private Sub butconf_Click()
With projectquote
If .txtstart = "" Or .cmbspec = "" Or .cmbgrade = "" Or .cmbage = "" Or .cmbreason = "" Or .txtdoc = "" Or .txthr = "" Or .txthrs = "" Or .txtreq = "" Or .cmbauth = "" Or .txtdoc = "" Then
MsgBox ("Not all boxes completed")
End If
Exit Sub

ActiveWorkbook.Sheets("database").Activate
Range("B2").Select
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cmbsite.Value
ActiveCell.Offset(0, 1) = txtstart.Value
For Each c In Selection
  With c
      .NumberFormat = "@"
      .Value = Format(.Value, "dd/mm/yyyy")
  End With
Next c
ActiveCell.Offset(0, 3) = txtend.Value
For Each c In Selection
  With c
      .Value = Format(.Value, "dd/mm/yyyy")
      .NumberFormat = "@"
  End With
Next c
ActiveCell.Offset(0, 4) = cmbspec.Value
ActiveCell.Offset(0, 5) = cmbgrade.Value
ActiveCell.Offset(0, 6) = cmbage.Value
ActiveCell.Offset(0, 7) = cmbage1.Value
ActiveCell.Offset(0, 8) = cmbreason.Value
ActiveCell.Offset(0, 9) = cmbvac.Value
ActiveCell.Offset(0, 10) = txtdoc.Value
ActiveCell.Offset(0, 12) = txthr.Value
ActiveCell.Offset(0, 13) = txthrs.Value
ActiveCell.Offset(0, 16) = txtreq.Value
ActiveCell.Offset(0, 17) = cmbauth.Value
ActiveCell.Offset(0, 20) = txtcom.Value
UserForm2.Show
End Sub

Thanks

Dave

 

[Kenneth Hobson]

Dave, this is a separate question so please start a new thread.