Macro to check for blank responses

[Guest]

I thought I would repost my question as I have not been able to figure this
one out.

I am using the code to check multiple controls on a single page of a
multipage userform. I have attached the code to a "Next" command button.
While the code that checks text boxes and comboboxes works well, the problem
is the grouped controls like Optionbuttons.

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.

Here is the code for checking blanks as it is 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

 

[Guest]

WillRn

It's been nearly three months since you posted this, so I figure you got it
worked out, but just for completeness sake:

Your For/next loop with the select case code loops through all the controls
on the form. Only the optionbuttons have the "GroupName" property (as far as
I know). So I think you get your error when the code attempts to determine
the groupname of your textboxes or your commandbuttons. The following if
statement surrounding your select code should take care of the problem.

For....
If TypeOf ctrl is MSForms.OptionButton Then
Select Case ...
End If
Next ...

You should be able to combine the process with your previous For/Next loop
that checks textboxes and comboboxes by using

For....
If....
ElseIf TypeOf ctrl is Msforms.OptionButton then
Select Case ...
End Select
End If
Next

As an aside I noticed that you use 'End For' to stop the for loop and go
back to editing on the form. This works well, but if you have any code
beyond the For/Next loop, that will execute as well every time there is an
error. For example, the way your code reads, if the user missed a textbox,
she would get the MsgBox "Missing answer, please complete," but if she also
missed an optionbutton, she would get the MsgBox "Question Missed, Please
Answer." At that point, I would be confused as to which two things I missed.
Further, this could be problematic if you use the form's textbox values to
enter information into the worksheet in code beyond the for/next loop.
Everytime you get the "Question missed" error, the code could potentially
make its entry. Then you would end up with multiple entries, which is
probably not what you want.

Instead, you can use the 'Exit Sub' code to stop execution of the
commandbutton_click() event and prevent any accidental code execution beyond
the "Question missed" msgbox.

drhalter

both of these answers/tips probably came at one point from Tom Ogilvy or Bob
Phillips or one of the other regulars on this forum.