Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check for blank responses
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check for blank responses
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. "WillRn" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Check? | Excel Worksheet Functions | |||
Check for Non Blank and Blank Cells Before Save | Excel Discussion (Misc queries) | |||
Macro to check if clipboard is blank. | Excel Discussion (Misc queries) | |||
Check for blank clipboard | Excel Discussion (Misc queries) | |||
Check if (part of) a row is blank. | Excel Programming |