ensure that all controls have a value

[ironj32]

I have a userform with multiple checkboxes, option buttons, textboxes, and combo boxes.

1)I would like to add a code that will ensure that all Enabled txtBoxes have a value in them. (I am assuming that the default value for all text boxes is Enabled unless modified by code.)

2) Also my different sets of option buttons are distinguished by GroupNames (each group simply consists of opt???Yes and opt???No)....need to have one of these = TRUE for each group.

3) My sets of checkboxes are also distinguished by GroupNames. I need to have atleast on chkBox = TRUE for each group.

4) I need to be sure that both of my cboBoxes are not Null

If everything is correct then i am going to run the following code.


Private Sub cmdSave_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("ContractWorkSheet")
'find first empty row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(irow, 1).Value = Me.txtUSBManager
ws.Cells(irow, 2).Value = Me.txtVendorName
ws.Cells(irow, 3).Value = Me.txtContractStartDate
...etc...
...etc...

Worksheets("ContractWorkSheet").Save
end sub


If Not Then I want a message box to appear telling them to complete the form.
Thanks a bunch!

[mdmackillop]

Can you post a workbook containing your userform?

[ironj32]

Sure thing, but how do i do that?

[mdmackillop]

Use Manage Attachments in the Go Advanced section

[ironj32]

Here you go. Thanks for your help.

[Bob Phillips]

I just took a quick squint at the form, so no solution from me IAA, but I think you should look at a Multipage control. A scrolling userform is bad news.

[mdmackillop]

Try the following. You might want to change the prefix for textboxes that can be excluded from the check. I've attached the code to your print button for testing.
Private Sub cmdPrint_Click()
Dim Str As String, OB As String, tmp As String, Ctrl As Control
For Each Ctrl In Me.Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "txt"
If Ctrl = "" Then
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "cbo"
If Ctrl = "" Then
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "opt"
Str = Ctrl.Name
If Right(Str, 2) = "No" Then OB = Mid(Str, 4, Len(Str) - 5)
If Right(Str, 3) = "Yes" Then OB = Mid(Str, 4, Len(Str) - 6)
If Me.Controls("opt" & OB & "No") = Me.Controls("opt" & OB & "Yes") Then
MsgBox "Please complete " & OB
Me.Controls("opt" & OB & "Yes").SetFocus
Exit Sub
End If
End Select
Next
End Sub

[ironj32]

awesome. thanks mdmackillop!

[ironj32]

what is so bad about a scrolling userform?

[Bob Phillips]

It's all to do with the user experience.

By having say a multipage control, the user immediately sees the various sections, but doesn't get overwhelmed by the complexity of the form, the amount of data. They fill in one part, and then move onto the next, so they exercise some of the control.

With everything presented on a one page form, it is a bit daunting, plus scrolling is not good, if you want to look back you have to go seeking. Just not good form design aesthetically speaking.

[mdmackillop]

I would agree with XLD. I found it a nuisance scrolling up and down, and I only had to do it two or three times while testing.

[ironj32]

The code that you gave me is working great. I have changed the optional text boxes to "tbxName", set their default values to Enabled = False, and i added the case statement:

Case "tbx"
If Ctrl.Enabled = False Then
ElseIf Ctrl = "" Then
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub

Thanks again!

[mdmackillop]

Glad to help. It shows the benefits of organised control names on a userform.

[ironj32]

i am curious to know how the option button part of this is working?

[mdmackillop]

The first part identifies the control.
Str = Ctrl.Name
If Right(Str, 2) = "No" Then OB = Mid(Str, 4, Len(Str) - 5)
If Right(Str, 3) = "Yes" Then OB = Mid(Str, 4, Len(Str) - 6)

The second part creates the names of both controls and compares them
If Me.Controls("opt" & OB & "No") = Me.Controls("opt" & OB & "Yes") Then

If they are equal, then nothing is checked.
Doing it with three or more would be a bit trickier. I had a quick look at GroupName, but it doesn't seem to have a value. Possibly using a frame would create a checkable value for the group. If you can come up with a solution, please let us know.

[ironj32]

ok, i made it so that the questionnaire is on three different tabs/pages. now when i run the command to complete/save and it checks if everything is complete i get an error at the "ctrl.setfocus" point of the code...this only happens if the control that it is trying to focus on is not on the page that currently has focus. is there away around this?

[Bob Phillips]

Can you post it?

[lilstevie]

IronJ,

Instead of all at the same time, try setting it up to validate each "tab" before proceding to the next one.

[ironj32]

here you go. once again, thanks for your help!

[lilstevie]

Private Sub MultiPage1_Click(ByVal Index As Long)
Dim Str As String, OB As String, tmp As String, Ctrl As Control
For Each Ctrl In Me.Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "tbx"
If Ctrl.Enabled = False Then
ElseIf Ctrl = "" Then
Me.MultiPage1.Value = 0
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "txt"
If Ctrl = "" Then
Me.MultiPage1.Value = 0
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "cbo"
If Ctrl = "" Then
Me.MultiPage1.Value = 0
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "opt"
Str = Ctrl.Name
If Right(Str, 2) = "No" Then OB = Mid(Str, 4, Len(Str) - 5)
If Right(Str, 3) = "Yes" Then OB = Mid(Str, 4, Len(Str) - 6)
If Me.Controls("opt" & OB & "No") = Me.Controls("opt" & OB & "Yes") Then
Me.MultiPage1.Value = 0
MsgBox "Please complete " & OB
Me.Controls("opt" & OB & "Yes").SetFocus
Exit Sub
End If
End Select
Next
End Sub

Will check page 1 when the user selects page 2, you will need to set it up for checking page 2 when the user selects page 3, and checking page 3 when the user saves or prints or.....

[mdmackillop]

Hi Stevie,
Thanks for pointing the way! I was trying Index, with no success.
I think this should check each page in turn, thus making the controls available. Let us know how you get on.
Private Sub cmdPrint_Click()
Dim Str As String, OB As String, tmp As String, Ctrl As Control
For i = 0 To 2
For Each Ctrl In Me.MultiPage1.Pages(i).Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "tbx"
If Ctrl.Enabled = False Then
ElseIf Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "txt"
If Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "cbo"
If Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Exit Sub
End If
Case "opt"
Str = Ctrl.Name
If Right(Str, 2) = "No" Then OB = Mid(Str, 4, Len(Str) - 5)
If Right(Str, 3) = "Yes" Then OB = Mid(Str, 4, Len(Str) - 6)
If Me.Controls("opt" & OB & "No") = Me.Controls("opt" & OB & "Yes") Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & OB
Me.Controls("opt" & OB & "Yes").SetFocus
Exit Sub
End If
End Select
Next Ctrl
Next i
End Sub

[mdmackillop]

Hi IronJ,
I think the Multipage looks a lot more "professional", so well worth the trouble to get this working.
You are requiring text in "additional comments" I noticed. Is this intentional?
With regard to Option Buttons and CheckBoxes, you can reset the Tag value of the enclosing frame when one is ticked. You can then check the Tag value of the frame to determine if anything has been checked.

[lilstevie]

Hey MD. Good one, looks like another to add to my reference. Someone needs to create a repository of all of these userform examples... they often get lost in the searches.

I seem to recall seeing some code to change the background color of the offending control to highlight the error. Should be easy to incorporate that into this sub also.

[lilstevie]

Using MDs SUB, something like this would highlight the blank cells yellow.

You may want to double check your control names, I found a couple that do not match your validation criteria. (unless you intended to exclude them from the checking)


Private Sub cmdPrint_Click()
Dim Str As String, OB As String, tmp As String, Ctrl As Control
For i = 0 To 2
For Each Ctrl In Me.MultiPage1.Pages(i).Controls
tmp = Left(Ctrl.Name, 3)

Select Case tmp
Case "tbx"
If Ctrl.Enabled = False Then
Ctrl.BackColor = &H80000005
ElseIf Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Ctrl.BackColor = &HFFFF&
Exit Sub

End If
Case "txt"
Ctrl.BackColor = &H80000005
If Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Ctrl.BackColor = &HFFFF&
Exit Sub
End If
Case "cbo"
Ctrl.BackColor = &H80000005
If Ctrl = "" Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & Ctrl.Name
Ctrl.SetFocus
Ctrl.BackColor = &HFFFF&
Exit Sub
End If
Case "opt"
Str = Ctrl.Name
If Right(Str, 2) = "No" Then OB = Mid(Str, 4, Len(Str) - 5)
If Right(Str, 3) = "Yes" Then OB = Mid(Str, 4, Len(Str) - 6)
If Me.Controls("opt" & OB & "No") = Me.Controls("opt" & OB & "Yes") Then
Me.MultiPage1.Value = i
MsgBox "Please complete " & OB
Me.Controls("opt" & OB & "Yes").SetFocus
Exit Sub
End If
End Select
Next Ctrl
Next i
End Sub

[mdmackillop]

Hi Stevie,
I thought I'd add this to the KB once it's fully sorted out. I like the colour change idea.

[ironj32]

Hi guys, thanks a ton...This is awesome! lilstevie, yeah i wanted to exclude a couple...since they are not going to pertain to every user/company.