Showing a userform in excel 2010 without knowing the name at runtime.

[tnkcoll]

Hello,
I am writing a program to track a person's training in excel 2010. New personnel will be added to this program every month. A new userform will be created for every person when they are added into this program and will be modified depending on the person's actions. I have no issues creating the form, my problem comes in when I try to show the form by choosing their name from a combobox. I can get it to work if I assign a name to the userform, but then I am restricted by what I can name the form. I am trying to add a first and last name, but if I don't type it perfectly I get an error message. I would like to reference the forms by their captions if it is at all possible. Here is what I have but it keeps giving me this error message: "run-time error '91' Object variable or With block variable not set":

Private Sub cboSupervisors_Change()
Dim i As Integer
Dim UserForm As Object

With ufHome 'a userform named "ufHome"
For i = 1 To 100
If UserForm(i).Caption = cboSupervisors.Value Then
VBA.UserForms.Add(UserForm(i)).Show
Exit For
End If
Next i
End With
cboSupervisors.Value = ""
End Sub


Any and all help will be greatly appreciated. Thank you.

[NoS]

Hello tnkcoll,

This situation is interesting as I can see a possible use myself. After searching and searching and more searching came across a Tom Ogilvy response to a question on another web site. Adapted it to a test workbook I set up and managed to get the name of the userform that would be used.

I don't have enough posts here to just give a link to the site so will include my test workbook. Comment in the macro gives credit to original site.

Hope this helps

NoS

[Ken Puls]

Cool deal, NoS, thanks for posting this.

[tnkcoll]

Thanks NoS, I need to do a little tinkering with it but this should work great! If I can't get it to work I will post my issues here. Thanks again.

[NoS]

tnkcoll,

What I posted shows that what you asked for can be done.

If your workbook has alot of userforms setting up the array of userform names will be the biggest obstacle.
Here is a routine that will extract the userform names without loading the individual forms so you can set up an array on a sheet to use.

Sub get_FormNames()

    Dim Count_xItems As Long
    Dim xItem As Integer
    Dim i As Long

    i = 2
    
Count_xItems = ThisWorkbook.VBProject.VBComponents.Count
    
    For xItem = 1 To Count_xItems
         'item.type 1 = Module
         'item.type 3 = Forms
         'item.type 100 = Worksheets
        If ThisWorkbook.VBProject.VBComponents.Item(xItem).Type = 3 Then 'Forms
            Debug.Print ThisWorkbook.VBProject.VBComponents.Item(xItem).Name
            'Cells(i, 3).Value = ThisWorkbook.VBProject.VBComponents.Item(xItem).Name
        i = i + 1
        End If
    Next xItem
    
End Sub

This code will result in Runtime Error 1004 Method 'VBProject' of object '_Workbook' failed if in the Excel macro security area, Visual Basic Project is not selected as a trusted source.

Myself I would try setting up a two column array on a sheet with the persons name next to the appropriate userform and use that to populate your original combobox with bonding to the second column. That way you wouldn't need to leave the trusted source open.

Any problems or questions, please post back

NoS

[Bob Phillips]

I have to say the design on this seems a## about face to me, why would you create a new userform for each person. That is a maintenance nightmare. I have never found such a design before and would love to know why you think this is necessary.

[NoS]

I do agree with Bob Phillips here.

I answered the question asked but that may not be beneficial.

tnkcoll, you may be should post an example workbook and perhaps we can save you a lot of maintenance headaches.

[tnkcoll]

This program is to keep track of training actions that need to be signed off by a trainee, trainer and for some actions, a certifier as well. The supervisor will be able to select their name from a dropdown menu that will bring up a userform with their trainee's names in a listbox. When they click on a name it will open up another userform that will show the status of their training actions. I am new to vba, as I am sure you will be able to tell. Any help you provide will be extremely appreciated. Thank you.

[Bob Phillips]

This seems like a simple worksheet to me. Just a list of names, and sign-off boxes.

[NoS]

Sorry tnkcoll but I don't understand what you are trying to do.
Every thing refers to sheetZ and it's blank.
What are Data1, Data2 and Data3 in the listbox referring to?
Would be easier to assist seeing what you're working with and how and where it's stored.

My gut feeling is that only 1 userform is going to be needed with a vlookup from a combobox to populate it.