Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Box as type of Control? (Word97/SR2)

    How would one go about a generic "clear all check boxes" routine on a GUI form?

    The code below is my skeletal effort to date.

    I can't seem to use a check box directly to loop through each of frmMe.Check Boxes.

    The Control for check boxes has a property called DisplayType, but VBE won't compile if I use that (and not all controls have it anyway).

    This problem seems to be part of an even more generic one - "How to loop through all controls of any type in a GUI form".

    A weak example would be a requirement that all Text Boxes have a Blue background, all Check Boxes are Red, all List Boxes Green, and so on; a routine would then cycle through all controls of a particular type setting the appropriate colours.

    My immediate needs are a tad more relaxed - I just wanted to clear all check boxes no matter what check boxes I was playing with during development.


    <pre>Public Sub Clear Switches(frmMe As UserForm)
    Dim ctl As Control
    For Each ctl In frmMe.Controls
    ' ?????????????????????????????????????????????
    Next cb
    End Sub
    </pre>


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

    Re: Check Box as type of Control? (Word97/SR2)

    You need something like

    <pre>if typeof ctl is Checkbox then
    ...</pre>

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

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    Or use TypeName as follows <pre>Dim oCtrl As Control
    For Each oCtrl In Me.Controls
    If TypeName(oCtrl) = "CheckBox" Then
    oCtrl = ""
    End If
    Next</pre>

    Andrew C

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    > TypeName(oCtrl) = "CheckBox"

    Thank you. Since there is no "property" of TypeName for a checkbox, I'm puzzled as to how you knew this? It works well, as my modified code shows:

    <pre>Public Sub ClearSwitches(frmMe As UserForm, strSetting As String)
    Dim ctl As Control
    For Each ctl In frmMe.Controls
    If TypeName(ctl) = "CheckBox" Then
    ctl = strSetting
    End If
    Next ctl
    'Sub TESTClearSwitches()
    ' frmSpell.Show
    ' frmSpell.Show
    ' Call ClearSwitches(frmSpell, " ") ' Indeterminate
    '' Call ClearSwitches(frmSpell, "0") ' Off
    '' Call ClearSwitches(frmSpell, "1") ' On
    ' frmSpell.Show
    'End Sub
    End Sub

    </pre>


  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    > if typeof ctl is Checkbox

    ... and I'm assuming that, like me, you know what you want but didn't have the explict means at hand? Please See my reply to Andrew.

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

    Re: Check Box as type of Control? (Word97/SR2)

    Typename is a VBA function, not a property of the control.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    TypeOf is valid VBA for use in conditions.

    From VBA help:

    The If...Then...Else statement syntax has these parts:

    Condition:

    One or more of the following two types of expressions:
    A numeric or string expression that evaluates to True or False. If condition is Null, condition is treated as False.

    An expression of the form TypeOf objectname Is objecttype. The objectname is any object reference and objecttype is any valid object type. The expression is True if objectname is of the object type specified by objecttype; otherwise it is False.

    ...

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    Right, but there's nothing in the help files to indicate its use as testing a control object, as far as I can see. Hence my question.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    Hans, thanks for the response.

    I'm missing something. I made a user form with an option button and a checkbox. The code below displays the first MsgBox correctly, identifying a CheckBox as it goes, but the If statement fails to generate a MsgBox.

    <pre>Sub test()
    Dim ctl As Control
    For Each ctl In UserForm1.Controls
    MsgBox Typename(ctl)
    If TypeOf ctl Is CheckBox Then
    MsgBox "checkbox!"
    Else
    End If
    Next ctl
    End Sub

    </pre>


  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    Chris,

    Try

    If TypeOf ctl Is MsForms.CheckBox Then ...

    Andrew

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    There is funny stuff going on here.

    In a UserForm in Word, TypeName(...) returns CheckBox, but in TypeOf ... Is ... you must use MSForms.CheckBox, just as you say.

    In an Access form (where I originally tried it out), the built in Access check box is of type CheckBox, but an MS Forms check box (the same used in the UserForm in Word) is of type CustomControl. This works with TypeName *and* TypeOf...

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    Also of interest is the fact that if it is a checkbox from the Forms toolbar (in a document or workshett), then TypeOf ctl Is Checkbox will work fine.

    Access forms and controls are different and perhaps Charlotte is the best person to explain them.

    Andrew

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Box as type of Control? (Word97/SR2)

    In the Help for IF:

    "An expression of the form TypeOf objectname Is objecttype. The objectname is any object reference and objecttype is any valid object type. The expression is True if objectname is of the object type specified by objecttype; otherwise it is False"

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

    Re: Check Box as type of Control? (Word97/SR2)

    Egads! Don't look to me for an explanation. I just thank my lucky stars for the differences and try to avoid the MSForms version.

    I can tell you, though, that Access controls are more like custom controls than the MSForms equivalents, with fewer exposed properties probably because they're customized to work with recordsets and database objects. Goodness knows what will happen to all us poor Access programmers when .Net finds its way into Office. <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>
    Charlotte

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

    Re: Check Box as type of Control? (Word97/SR2)

    Well, I'm looking at 2000 help, but it says "Returns a String that provides information about a variable." One of the string returned values is objecttype where the variable is an object of that type. To me that translates to "pass it a control of some type and it will tell you what type it is." <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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