Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 -- regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 "Tom Ogilvy" wrote: 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 -- regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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" wrote: 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 -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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 -- jojotherider ------------------------------------------------------------------------ jojotherider's Profile: http://www.excelforum.com/member.php...o&userid=24548 View this thread: http://www.excelforum.com/showthread...hreadid=350456 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking a complex userform for blank entries
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" wrote in message news:jojotherider.1tkjqk_1123711524.4467@excelforu m-nospam.com... 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 -- jojotherider ------------------------------------------------------------------------ jojotherider's Profile: http://www.excelforum.com/member.php...o&userid=24548 View this thread: http://www.excelforum.com/showthread...hreadid=350456 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking For Duplicate Entries in a Userform | Excel Discussion (Misc queries) | |||
Checking Column Entries | Excel Worksheet Functions | |||
Checking entries against a column of data | Excel Discussion (Misc queries) | |||
Checking for double entries | Excel Discussion (Misc queries) | |||
Checking for duplicate entries | Excel Discussion (Misc queries) |