Getting the captions of UserForms that are closed

  • For reasons of documentation, I'd like to get a name list of all my UserForms and their captions. I can get the name list, following what I found here: Looping through UserForms


    But I can't figure out how to get the caption. In the following, the caption getting returns a 438 error.


    Code
    1. Dim objComp As Object
    2. Const vbext_ct_MSForm = 3
    3. For Each objComp In ThisWorkbook.VBProject.VBComponents
    4. If objComp.Type = vbext_ct_MSForm Then
    5. Debug.Print = objComp.Name & "," & objComp.Designer.Controls.Caption
    6. End If
    7. Next
  • donwiss, welcome to Ozgrid. Please read the Forum Rules to understand how the Forum works.


    I have added Code Tags this time.


    Try this. You need to set a reference to "Microsoft Visual Basic For Applications Extensibility 5.3".


    1. Open the VB Editor
    2. Click Tools
    3. Click Referces
    4. Scroll down and check the required item
    5. Click "OK"
  • To get the form names, control names and caption names output to a sheet try this:



    The on error resume next is needed because the ctrl.caption will fail on controls that do not have a caption, there is probably a better way to catch it.

    You also need to ensure the trust center setting has the "TRUST access to the VBA project object model" ticked.


    HTH

  • To RoyUK:


    Thanks for adding the tags. But looking up and down on this page I see no tags. Being an Excel VBA question in an Excel VBA forum, I'm not sure how tags can further define the topic.


    I ran the code you posted. It returns a pretty list of all the form names. But what I wrote already does this. I can also simply look at my Project window. What I want is a list of the 60 form names with captions, so one can tell what each form does.



    To Justin Doward:


    Getting the captions on all controls would produce thousands of lines of useless information. I tried running the code you posted. I get a variable not defined on HELPER.


    What I think I need to do is to find what If statement under this captures the form caption I want:


    For Each ctrl In ufs.designer.Controls

  • Justin,


    Cool it, please. If you can't say something nice, don't say anything at all.


    Don,


    I suggest you re-read your posts, try and put yourself in the shoes of someone volunteering their time to try and help you, and see how you think those posts come across. There's a pretty simple rule on forums: if you want help, it pays to be nice. Currently your tone definitely doesn't encourage me to try and assist you, and I doubt I'm alone.


    Thank you both. :)


    PS This isn't up for discussion. If you have an issue with it, feel free to contact me privately rather than hijacking this thread further.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • To answer the posed question, I think this solves the issue:


    Code
    1. Sub CaptionRead()
    2. Dim ufs As Object
    3. On Error Resume Next
    4. For Each ufs In ThisWorkbook.VBProject.VBComponents
    5. Debug.Print "Userform name = " & ufs.Name & ", Caption = " & ThisWorkbook.VBProject.VBComponents(ufs.Name).Properties("Caption")
    6. Next
    7. End Sub

    Sorry for the previous post, it was in the AM my time and my eyes were a little crooked. Feel free to remove it.

  • Done, and thank you for the acknowledgement. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Here's the "copy ready" version. Hopefully those web searching, and getting to here, will find this useful.