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

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
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

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

Attachments

  • RunTimeLoad_UserForm.xls
    60.5 KB · Views: 102

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
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

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.

Code:
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 
Last edited:

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
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.
 

Attachments

  • AFTR Status.xlsm
    23.5 KB · Views: 62

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
This seems like a simple worksheet to me. Just a list of names, and sign-off boxes.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
SheetZ will be where the names that will be in the combobox are stored when they are added. The 3 Data items in the listbox are just fillers for right now, the listbox will hold the names of the personnel assigned to the supervisor in the combobox. Sorry for the confusion with all of this.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
tnkcoll

Sorry buds, would like to assist but just don't understand what you're trying to do.
Looks like create a bunch of userforms all the same except for the caption.
And what you plan to do or have on those forms, I have no idea.

I'm not able to create the userform via module procedure but made an approximation for display.
And put some 'stuff' on your Z-sheet for something to play with.

If this helps at all that's good. If not I'm sure you know where the Delete key is.

Good luck with your project
NoS
 

Attachments

  • Altered_Status.xlsm
    28.3 KB · Views: 27

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
Thank you NoS, your example gave me a new direction to go. It doesn't look like I will need all those extra userforms now. If I have anymore problems I will post them here. Thank you again.
 

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
I ran into another problem,
I am trying to assign a trainee to a supervisor in userform1. I enter the trainee's name in a textbox called "txtTraineesName" then hit the "ADD" button and want the trainee's name to go into the first empty row of column B and their supervisor's name to go into the same row in column C. The supervisor's name is the caption of userform1. I tried researching this and came up with nothing, maybe I'm looking in the wrong places. I thought vlookup would be a good route to go since all the supervisor's names will be in column A, but I will be sorting this row as new supervisors are added, so they will not always be in the same cell. Thank you in advance for all your help.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Ron de Bruin has a very useful web site for VBA things. Check out http://www.rondebruin.nl/last.htm to see about finding last used row and column.

'get the last row used in column B
Last_row_used_in_B = Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

'the next available row where you will add your info
RowToUse = Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row + 1

Cell for added trainee: Cells(RowToUse,2).value = txtTraineesName.text
Cell for supervisor: Cells(RowToUse,3).value = Userform1.Caption

tnkcoll you'll have to play around with this regarding the userform being loaded and the correct worksheet active.
Hope this is of some assistance.
NoS
 

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
Thanks NoS. I have another problem I need assistance with. I know I'm wasting a lot of your time and I feel bad about it. I've searched for answers elsewhere and can't come up with any. If you have some more websites you can direct me too for learning VBA so I don't waste anymore of your time I would apreciate it. My problem now is a bit complex(for me), I have it where everytime a new supervisor is added, it creates a new worksheet for that supervisor and then the ADD button on userform1 will add trainees to that supervisor's worksheet. That part works great, but here's the problem, everytime I assign a trainee to a supervisor I need cells D-K from Sheets("Z") to be copied to the supervisor's worksheet starting with the next empty column. I looked through the website you talked about in your last post and I couldn't figure out this problem. Thank you for all the help you've given me, sorry for being such a burden to you.
 

Attachments

  • Altered_Status In Work.xlsm
    42.8 KB · Views: 22
Last edited:

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
Thanks Nos, I found my answer. You are awesome. I can't thank you enough for all the help you've given me. I will post my project here when it is complete in case others have the same issues I did. Thanks again.
 
Last edited:

tnkcoll

New member
Joined
Jan 19, 2013
Messages
9
Reaction score
0
Points
0
NoS, I have one last problem that I can't solve, I've looked through the links you gave me but got nowhere with this particular subject. I learned a lot from them though. I have created a list of dropdown menus in sheets(Z) that are copied to the supervisor's sheet when a new trainee is added. I need to somehow link these dropdown menus with the corresponding trainee's name in the same sheet. Then, when the trainee's name is selected from the combobox on userform1, the item numbers to the left of the dropdown menus in the supervisors sheet will be written to the listbox on userform1. This should be the last time I bother you, everything else I have learned how to do on my own. Thank you for everything.
 

Attachments

  • Altered_Status In Work.xlsm
    64 KB · Views: 20
Top