Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    See Form Controls in VBA? (2000 sp3)

    Is there a way to "see" the controls on a form in VBA without adding the form to the UserForms collection? When I do that, it executes the _Initialize event, which I don't want to do. (I guess if there was a way to suppress that from happening I could go that way too.)
    I can "see" the form when I loop through ActiveWorkbook.VBProject.VBComponents and I can see the CodeModule beneath that but I can't figure out how to see the controls of the form.
    Any help would be most appreciated!
    Thanks

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

    Re: See Form Controls in VBA? (2000 sp3)

    You can use the Designer object. For example:

    Debug.Print ActiveWorkbook.VBProject.VBComponents("UserForm1") .Designer.Controls.Count

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: See Form Controls in VBA? (2000 sp3)

    Great! Thank you very much, Hans!

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: See Form Controls in VBA? (2000 sp3)

    I'm still not there yet... I can get ...designer.count and ...designer.name but I can't figure out any other properties like "Type" to determine what kind of control it is.
    I tried this:

    dim objForm as UserForm
    set objForm = ActiveWorkbook.VBProject.VBComponents("frmMyForm") .designer
    For each objControl in objform.controls
    objControl.
    Next

    but the properties it shows for objControl. doesn't include "Type". How can I tell the difference between a TextBox and a CommandButton?
    Thanks for your patience and help.

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

    Re: See Form Controls in VBA? (2000 sp3)

    Something like this

    Dim ctl As Control
    For Each ctl In ActiveWorkbook.VBProject.VBComponents("frmMyForm") .Designer.Controls
    Debug.Print ctl.Name, TypeName(ctl)
    Next ctl

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: See Form Controls in VBA? (2000 sp3)

    You are the best! Thanks! Works great.

Posting Permissions

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