Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    683
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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
    Attached Files Attached Files

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Cool deal, NoS, thanks for posting this.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    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.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    683
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  6. #6
    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.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    683
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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 by NoS; 2013-01-25 at 09:10 PM.

  8. #8
    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.
    Attached Files Attached Files

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

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    683
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

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