Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking Whether All ComboBoxes Are Filled

    In Word 97 template, I have a userform that appears containing several comboboxes that appear blank but the user should choose an item for each one. I know how to do code that checks each combobox separately by name and sets the focus back to it if it is empty but I am wondering is there a way my code can be steamlined with a For..Each statement that would do the same routine without each combobox having its own separate staement. An example of my code is below. It is just repeated for all the comboboxes in my userform. Your help is always greatly appreciated.

    If ComboBox1.Text = "" Then
    MsgBox ("Please Choose A Name!")
    ComboBox1.SetFocus
    Exit Sub
    End If

    Gary

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    I usually do my work in Access but its all VBA right?
    This should work.

    Dim ctl As Control
    Ctl.ControlType = acComboBox

    For Each ctl In Me.Controls
    If ctl.Value =

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Hi,

    Just to add a small tip to Bill's: you may also want to further narrow down which controls get acted upon in the For Each loop by using:

    If ctl.Type = msoControlComboBox Then
    'test for Value.....

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Gary and Bill,

    thanks for your help but I get a Runtime eror 91 when I try to run the code. What adjustments should I make to this? Thanks again.

    Here is the code.
    Dim ctl As Control
    ctl.ControlType = acComboBox


    For Each ctl In Me.Controls
    If ctl.Type = msoControlComboBox Then

    If ctl.Value =

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Hi Gary,

    I apologize - I'm having one of those Twilight Zones experiences [img]/w3timages/icons/hairy.gif[/img] - I thought I got a ctl.Type statement working in Word 97, (didn't save the scratch code) - now when I go back to look again - no ctl.Type! - spooky.

    And I also didn't notice earlier that Bill's code specified the control type with an "acComboBox" constant, that doesn't exist in Word, so we can't use it here.

    Until we can figure out how to get that Control.Type thing working in Word, you actually don't want to use the For Each Control method, since this is going to loop through every control on the userform. And some of those controls will not support a .Value or .Text property which will raise an error.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Change the ctl.Type line to:
    If TypeOf ctl Is ComboBox Then


    Full code:
    <pre>Dim ctl As Control

    For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
    If ctl.Value = "" Then
    MsgBox ("Please Choose a Name!")
    ctl.SetFocus
    Exit For
    End If
    End If
    Next
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Hi Geoff,

    I got really excited when I saw your "TypeOf" construct - but it appears this pertains to CommandBar controls and not userform controls - you can't raise a .Text or .Value property of a ComboBox control object that's defined this way[img]/w3timages/icons/sad.gif[/img].

    Gary

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Thanks Gary. If you find out anything, I will look forward to hearing of it. It would definitely save a lot of lines of code.

    Gary

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Gary,

    It worked for me in a userform (Excel 2000 and Word 2000).

    However, you're right about the value. Perhaps this will work better:
    <pre>Dim ctl As Control

    For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
    If ctl.ListIndex = -1 Then
    MsgBox ("Please Choose a Name!")
    ctl.SetFocus
    Exit For
    End If
    End If
    Next
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Hi,

    When I put this code into something like cmdOK_Click, it prompts for the first empty combobox. If I add text into the first combobox, the only other action I can take (besides filling in all the other comboboxes), is to click on cmdOK again. When I do this, it just takes me back to the first combobox, even though this one now has text.
    The only way out of this loop is to close the form by clicking on the "X".

    I still don't think VBA is really recognizing "ctl" as a userform control. If you type "ctl.", the autocomplete list is the one pertaining to a commandbar control.
    And if you type "Me.ComboBox1.", the autocomplete list is completely different from the one for ctl (it's the one you'd expect in relation to a userform combobox).

    (At this point I'm doing a better job of critiquing than solving[img]/w3timages/icons/grin.gif[/img])
    (And I have a vague recollection of having once written code that looped through all the controls in a userform, filtering them by type, but no idea where that code lives now - arrgh.)

    Gary

  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Gary,

    Hey, fair go mate, that was a solution to a part of the problem- not the solution to the entire application![img]/w3timages/icons/laugh.gif[/img]

    If you want the process to end, put in whatever processing you want after the end of the cmdOK processing- eg, just the "end" statement (or me.Hide) will close down the dialog box- but that would be pretty useless, because you haven't done anything with the processing you've done so far.

    The autocomplete list is probably misleading here. It's probably similar to late binding- because the properties and methods can vary accoring as to what sort of control you have (and I think it can encompass a lot more the userform controls), then the autocomplete cannot autocomplete- ie, the properties are only available at runtime; if a property is incorrect for that control, you only find out at runtime.

    So although "listindex" does not show up as a valid value in the autocomplete, it will not fall over with a combobox control. It will probably fall over with a label control, for instance.

    Us computer people aren't all that great explaining in clear English, I hope that made sense.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checking Whether All ComboBoxes Are Filled

    Guys, can I jump in here? The reason your ctl variable shows a different list of properties and methods from a combobox is because the ctl variable was dimmed as a Control, which has only the generic properties and methods available to any control--stuff like name. If you want to use a For each ctl in me.Controls loop, and deal with a specific type of control, you also need to dim a variable as that type, like dim cbo as ComboBox, and then stick in a line that says ...

    Set cbo = ctl

    That gives you a combobox object with the combobox properties to deal with.

    BTW, what you were looking for is ControlType, which is a property of Access controls, or the TypeOf operator in VB.
    Charlotte

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Geoff,

    This code worked for me in my Word 97 template! Thanks so much. I simply replace the Exit For line with Exit Sub and I did not receive any runtime errors. When a combobox was empty, I got the MsgBox but when all were filled they were placed into the document.

    Fine work!

    Gary

  14. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Checking Whether All ComboBoxes Are Filled

    Thanks, Charlotte! ([img]/w3timages/icons/yikes.gif[/img][img]/w3timages/icons/doh.gif[/img])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •