Excel Userform: certain fields need to be entered

[Mattlake]

Hi

I have tried to search and made several attempts (that all failed).

I have a Userform with a script that works. I have a command button to submit onto the spreadsheet which all works. I have found that the person using the form is missing some fields such as month and dept.
I want to add a bit of script to make Combobox1, Combobox5 are mandatory and a message pop up saying to complete them.

any help would be appriciated, below is the code i have done (with a lot of help from your good selves)

Regards
Matthew

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("2023")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws
.Cells(iRow, 1).Value = Me.ComboBox2.Value
.Cells(iRow, 2).Value = Me.ComboBox1.Value
.Cells(iRow, 3).Value = Me.TextBox1.Value
.Cells(iRow, 4).Value = Me.ComboBox3.Value
.Cells(iRow, 5).Value = Me.TextBox2.Value
.Cells(iRow, 6).Value = Me.ComboBox4.Value
.Cells(iRow, 7).Value = Me.ComboBox5.Value
.Cells(iRow, 8).Value = Me.ComboBox6.Value
.Cells(iRow, 10).Value = Me.ComboBox7.Value
.Cells(iRow, 11).Value = Me.ComboBox8.Value
.Cells(iRow, 12).Value = Me.TextBox3.Value
.Cells(iRow, 13).Value = Me.TextBox4.Value
.Cells(iRow, 14).Value = Me.TextBox5.Value
.Cells(iRow, 15).Value = Me.ComboBox9.Value
.Cells(iRow, 16).Value = Me.ComboBox11.Value
.Cells(iRow, 17).Value = Me.ComboBox12.Value
.Cells(iRow, 18).Value = Me.ComboBox10.Value
.Cells(iRow, 20).Value = Me.ComboBox13.Value
.Cells(iRow, 9).Value = .Range("F" & iRow) & " " & .Range("G" & iRow) & " " & .Range("H" & iRow)
.Cells(iRow, 19).Value = .Range("P" & iRow) & " " & .Range("Q" & iRow) & " " & .Range("R" & iRow)
.Cells(iRow, 21).Value = Me.TextBox6.Value


End With
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.ComboBox8.Value = ""
Me.ComboBox9.Value = ""
Me.ComboBox10.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox12.Value = ""
Me.ComboBox13.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""



End Sub

Private Sub cmdclose_Click()
Unload Me
End Sub



Private Sub Control1_Click()

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub ComboBox2_Change()

End Sub

Private Sub ComboBox7_Change()

End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = [other!A2:A20].Value
    ComboBox2.List = [other!d2:d6].Value
    ComboBox8.List = [other!L1:L8].Value
    ComboBox4.List = [other!H2:H32].Value
    ComboBox5.List = [other!I2:I13].Value
    ComboBox6.List = [other!J2:J8].Value
    ComboBox3.List = [other!A2:A20].Value
    ComboBox7.List = [other!N1:N8].Value
    ComboBox9.List = [other!f1:f2].Value
    ComboBox11.List = [other!H2:H32].Value
    ComboBox12.List = [other!I2:I13].Value
    ComboBox10.List = [other!J2:J8].Value
    ComboBox13.List = [other!f1:f2].Value
    
    
End Sub

 

[Micron]

How many controls do you want to validate? If only 2 or 3 then check them individually at the beginning:

If Combo1 = "" Then
   msgbox "Please provide a value for whatever"
   Exit Sub
End If

I hit save button by accident so editing now....

Not very efficient or user friendly - there'd be a prompt one by one, which can be annoying.
I prefer to use the control Tag property (e.g. set it to Req [for Required] ) and loop over the controls. If the control has no data and its tag is Req then I'd add it to a list and message the list at the end of the loop, then Exit Sub.

 

[Mattlake]

Thank you it is only 2 I need these control some count ifs to give totals on a seperate page (combobox 1 and 5)

 

[Mattlake]

Hi I have put this at the top (assuming that it runs in order so before it transfers) but i am sure i need to do something else but when i test it, there seems to be an error. this is where i have put it.

Im sure that i have missed something so simple before the code

Private Sub cmdAdd_Click()

If ComboBox1 = "" Then
   MsgBox "Please provide a Directorate responsible for incident"
If ComboBox5 = "" Then
    MsgBox "Please provide month of incident"
   Exit Sub
End If

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("2023")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 

[Micron]

Each If needs and End If unless the statement is on one line only. So depending on what you want to happen:

If ComboBox1 = "" Then 
   MsgBox "Please provide a Directorate responsible for incident"
   Exit Sub
End If

If ComboBox5 = "" Then
   MsgBox "Please provide month of incident"
   Exit Sub
End If

or perhaps

If ComboBox1 = "" Or ComboBox5 = "" Then 
   MsgBox "Please provide a responsible Directorate and month for incident"
   Exit Sub
End If

This time it seems fairly obvious, but its helpful if you provide the error number (if there is one) and the message you get.

 

[Mattlake]

thank you that is perfect

 

[Micron]

You're welcome. Glad to help & thanks for the recognition.