Checking a complex userform for blank entries

[WillRn]

I have a complex userform that I would like to be checked for blank entries
upon posting. The following code does great as long as I am just looking at
Textboxes or just one kind of control.

Dim ctl

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Text = "" Then
MsgBox "Missing answer, please complete"
ctl.SetFocus
Exit For
End If
End If
Next ctl

However, my user form has several comboboxes and several groups of
OptionButtons (Yes, No, NA) that I would like checked also. Is there any way
to check everything on the page at once?

WillRn

 

[Tom Ogilvy]

For Each ctl In Me.Controls

If Typeof ctl is MSForms.TextBox Then
If ctl.Text = "" Then
MsgBox "Missing answer, please complete"
ctl.SetFocus
Exit For
End If
ElseIf TypeOf ctl is MSforms.combobox then

ElseIf TypeOf ctl is MSForms.Checkbox then

end if
Next ctl

 

[WillRn]

As always, thanks Tom.

Just one question though, how do I get it to check a group of optionbuttons
(controls that have the same GroupName). In other words, the question has
three option buttons assigned in a group. Only one of the three buttons need
be checked/answered. e.g. yes, no, NA.

The user checks "Yes," one of the questions in the GroupName has been
answered so the code goes to the next group of controls or textbox, . . . etc.

 

[Tom Ogilvy]

for purposes of demonstration, assume to groups of option buttons
1st group name is Grp1, number of buttons: 3
2nd group name is Grp2, number of buttons: 5

Dim cnt1 as Long, cnt2 as Long
Dim bOp1 as Boolean, bOp2 as Boolean

cnt1 = 0: cnt2 = 0
bOp1 = False: bOp2 = False
For Each ctl In Me.Controls

If Typeof ctl is MSForms.TextBox Then
If ctl.Text = "" Then
MsgBox "Missing answer, please complete"
ctl.SetFocus
Exit For
End If
ElseIf TypeOf ctl is MSforms.combobox then

ElseIf TypeOf ctl is MSForms.Checkbox then

ElseIf TypeOf ctrl is MSForms.OptionButton then
Select Case Ctrl.GroupName
Case "Grp1"
cnt1 = cnt1 + 1
if Ctrl.Value then bOp1 = True
if cnt1 = 3 then
if not bOp1 then
msgbox "Select at an Option for Payment"
ctl.SetFocus
exit for
end if
Case "Grp2"
cnt2 = cnt2 + 1
if Ctrl.Value then bOp2 = True
if cnt2 = 5 then
if not bOp2 then
msgbox "Select at an Option for Currency"
ctl.SetFocus
exit for
end if
End Select

end if
Next ctl

 

[WillRn]

Thanks for the code Tom.

Unfortunately, I keep having a few of problems.

First, I am putting this code onto a Multipage form. Is there a way I can
have it check only one page of the multipage at a time? Because, when the
code that checks just comboboxes and textboxes code runs, I get a message of:

"Run-Time Error: '2110'

Can't move the focus to the control because it is invisable, not enabled, or
of a type that dow not accept focus.

I assume that this is because the control that it is trying to focus on is
not visable. I was going to attach the code to a "Next" command button and
have it check the controls on only the visable portion of the multipage. Then
go to the next page.

The other issue is when I try to run the code that checks the optionbutton
groups.
I get a compile error that states "case without select case."

Thanks again Tom for putting up with a newbie on this.

Any advice would be very welcome.

WillRn

 

[Tom Ogilvy]

idex = UserForm1.MultiPage1.Value
For Each ctrl In UserForm1.MultiPage1 _
.Pages(idex).Controls
'MsgBox ctrl.Name


I don't know what to tell you.

Merging your code with mine, two end if 's were ommited:

Select Case Ctrl.GroupName
Case "Grp1"
cnt1 = cnt1 + 1
If Ctrl.Value Then bOp1 = True
If cnt1 = 3 Then
If Not bOp1 Then
MsgBox "Select at an Option for Payment"
ctl.SetFocus
Exit For
End If
End If
Case "Grp2"
cnt2 = cnt2 + 1
If Ctrl.Value Then bOp2 = True
If cnt2 = 5 Then
If Not bOp2 Then
MsgBox "Select at an Option for Currency"
ctl.SetFocus
Exit For
End If
End If
End Select

 

[WillRn]

I think that this is really close to working, thanks for your patience Tom.

I get a "Run-Time Error 438" "Object doesn't support this property or
method." occurring at the "Select Case ctrl.GroupName" line in the code.

Any ideas?

Here is the code as I have it now:

Dim cnt1 As Long, cnt2 As Long
Dim bOp1 As Boolean, bOp2 As Boolean

cnt1 = 0: cnt2 = 0
bOp1 = False: bOp2 = False

Index = CMTAudit.AuditDataMultipage.Value

For Each ctrl In CMTAudit.AuditDataMultipage.Pages(Index).Controls
'MsgBox ctrl.Name
If TypeOf ctrl Is MSForms.TextBox Then
If ctrl.Text = "" Then
MsgBox "Missing answer, please complete"
ctrl.SetFocus
Exit For
End If
ElseIf TypeOf ctrl Is MSForms.ComboBox Then
If ctrl.Text = "" Then
MsgBox "Missing answer, please complete"
ctrl.SetFocus
Exit For
End If
End If
Next ctrl


For Each ctrl In CMTAudit.AuditDataMultipage.Pages(Index).Controls

Select Case ctrl.GroupName
Case "DoubleID"
cnt1 = cnt1 + 1
If ctrl.Value Then bOp1 = True
If cnt1 = 3 Then
If Not bOp1 Then
MsgBox "Question Missed, Please Answer"
ctrl.SetFocus
Exit For
End If
End If
Case "FallMeasures"
cnt2 = cnt2 + 1
If ctrl.Value Then bOp2 = True
If cnt2 = 5 Then
If Not bOp2 Then
MsgBox "Question Missed, Please Answer"
ctrl.SetFocus
Exit For
End If
End If
End Select
Next ctrl
End Sub

 

[jojotherider]

Hi,

I have a question about the validation in this code and the overall
form.

I've been able to get this to run (sort of), but when the user gets the
message that information is missing and they click OK, the macro just
continues on. Is there a way to make it go back to the form?

thanks for your help!

Joel

 

[Henry]

jojotherider,

Copy and paste your code into the body of your post (Not an attachment).
We'll then be able to see what you're talking about and give you some help.


Henry


"jojotherider" <[email protected]>
wrote in message
news:[email protected]...