Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    UserForm Name (Excel 97 and >)

    Folks

    I was asked this question:

    Can one display a Userform by using its name as a variable? In other words what would be the syntax for something like:
    <font color=red> UserForm("xxx").show </font color=red>

    Consider this:

    You have a table driven application, where you would set all name of all objects that the application needs. Then you want to call these values and use them in your code.

    Thanks for any help

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Name (Excel 97 and >)

    Wassim,

    To respond to your second paragraph, one application I made, I stored settings on a sheet, but used a form to get the results from the user. In the first column were labels that exactly matched the control names in the form. Each subsuequent column contained various permutations of settings. In the Activate event code for the form, I looped through the range of labels, then applied the value from the desired value column to the control. Then, for the Click event for the OK (or Apply) button, I would do the opposite - loop through the range of labels and apply the control value to the column containing the values that will actually be used for calculations later.

    Advantage: folks with no programming knowledge could easily look up what settings were in place when they did something in the workbook.

    Disadvantage: As the project expanded, more and more options were added. It takes significant time to populate the form with values this way, and to extract the values when OK is clicked, even with ScreenUpdating turned off.

    As for you original question, it looks like the best you can do is:

    <pre>VBAProject.UserForm1.Show
    </pre>


    There is no UserForms collection in VBAProject, so you can't do a For Each F in VBAProject.UserForms, checking the names as you loop. There is no external name (like there is for worksheets and charts) that is visible from the Excel interface, only the internal name. However, you can edit internal name in the VBE, of course.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Name (Excel 97 and >)

    Hi Wassim,

    Once you have loaded all userfoms, you can access them using their index number (beware: the index starts at zero, in the order you load them. In code:

    Sub test()
    Load test2
    Load test1
    UserForms(0).Show
    End Sub

    shows Userfom test2

    Sub test()
    Load test1
    Load test2
    UserForms(0).Show
    End Sub

    Shows userform test1
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    pieterse

    Thanks a lot for your reply. Unfortunatly if you have a lot of forms, that is a lot of loading and a lot of RAM being used.

    Essentially when you do something like <font color=blue> Worksheets("<font color=red> NameOfWorksheet </font color=red>").Select </font color=blue> you can have anything in the <font color=red> string </font color=red>, but you can't do the same with a UserForm.

    Or if you can, then what would be the syntax.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    Jim

    Thanks for your reply. I am still stumped with this and I am not sure there is an easy and elegant solution.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    Jim

    See my reply into this thread. Kinda cool.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    OK All

    I have figured it out... Oh boy here goes:

    Sub ShowVariableNameUserForm()
    Dim sUserFormName As Variant

    sUserFormName = ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e
    VBA.UserForms.Add(sUserFormName).Show
    End Sub

    Now surely you will have these UserForms designed and ready to go, so how does this "Add" thing works is surprising, and how come it does not give you any indication that the object you are "adding" already exist? This came from the MSKB Q157609.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    There's a UserForms collection:<pre>Dim oForm As Object
    Set oForm = UserForms.Add("UserForm2")
    oForm.Show</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Name (Excel 97 and >)

    I think that when you create a "userform" in the VBE, you are actually creating a special class module. That is, the form is the class, and the instances of it are added to the UserForms collection at run time. For example, I create one "userform" in the VBE and changed the ShowModal propery to False, then ran the following code:

    <pre>Sub Test()

    Dim oForm1 As Object, oForm2 As Object

    Set oForm1 = UserForms.Add("frmClassy")

    Set oForm2 = UserForms.Add("frmClassy")

    With oForm1
    .Caption = "First"
    .Show
    End With

    With oForm2
    .Caption = "Second"
    .Show
    End With

    MsgBox UserForms(0).Name
    MsgBox UserForms(1).Name

    End Sub
    </pre>


    What I end up with is two instances of the form on screen at once, with different captions. However, the MsgBox results each yield "frmClassy", which is the Name property from the property sheet for the form in the VBE. So, I can refer to each one independently by index number, object variable (oForm1, oForm2), or by inspecting the caption property (or any other distinguishing property) at run time.

    This has been a very interesting thread for me, thanks all!

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    Sammy

    I wish you chimed in earlier <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm Name (Excel 97 and >)

    I'm not in this week! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Name (Excel 97 and >)

    Hi Wassim,

    Hey, that is nice. Good searching.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Name (Excel 97 and >)

    Hi Jim,

    That was a good example. Nice to know this!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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