Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA Reference Dynamic Userform Name

  1. #1
    Join Date
    1st December 2006
    Posts
    22

    VBA Reference Dynamic Userform Name

    Hi guys, strugled with the name a little bit, but I think it is pretty accurate.

    This is more a "for interest" question as I have thought of another way to get around the problem, but I still wouldn't mind knowing if it is possible.

    I started out with a program that allows me to enter in the parameters for up to ten different variations of a design. Originally these were entered on one userform within different multipages for each variation. Once all the values were entered for each multipage, the values in the controls were entered into the spreadsheet, calculated, then returned to the userform in a results section. The code for the values being entered into the spreadhseet and the updating of the results was all in a public module, called by an OnClick command. The data is organised in columns in the spreadsheet (one design per column), with the names of the cells in each row being the same, but with the number from 1 to 10 at the end of the name (ie. result1, result2...).

    This mean that the updating for each design was done within one pretty simple module similar to;
    VB:
    Range("data" & i).Value = Userform1.Controls.item("data" & i).Value 
    
    
    However, there were too many controls and I was getting an out of memory error when running the userform. Hence, I split up the userforms so there was one per design. This became a problem though, when I realised the public module would have to be repeated for each userform, as the above code shows the userform2 code would be;

    VB:
    Range("data" & i).Value = Userform2.Controls.item("data" & i).Value 
    
    
    I have tried some code at the start to try and make the name of the userform variable, but none of the following seemed to work for me;

    VB:
    Dim usef As userform 
    Set usef = userforms("userform" & i) 
     
     'or
     
    Dim usef As userform 
    Dim namef As String 
    namef = "userform" & i 
    Set usef = userforms(namef) 
    
    
    After that and a few other silly ideas, my limited understanding ran out. Is it possible to have a variable reference to a userform name? Or am I dreaming?

    Otherwise, the way I will get around it is to call a single userform, set the design number when entering the userform, then use this to change the spreadsheet references, but keep the userform reference the same;

    VB:
    Range("data" & i).Value = userform1.controls.data 
    
    
    Sorry this is such a long post, but I felt it necessary to explain the whole situation. Looked everywhere for an answer to this, including some VBA for excel programming manuals & still couldn't find anything.

    Cheers,
    Evolvd

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Subroutine That References A Dynamic Userform Name

    VB:
    Public Sub ShowUserFormByName(FormName As String) 
        Dim oUserForm As Object 
        On Error Goto err 
        Set oUserForm = UserForms.Add(FormName) 
        oUserForm.Show 
        Exit Sub 
    err: 
        Select Case err.Number 
    Case 424: 
            MsgBox "The Userform with the name " & FormName & " was not found.", vbExclamation, "Load userforn by name" 
    Case Else: 
            MsgBox err.Number & ": " & err.Description, vbCritical, "Load userforn by name" 
        End Select 
    End Sub 
    
    
    HTH

    Bob

  3. #3
    Join Date
    1st December 2006
    Posts
    22

    Re: Subroutine That References A Dynamic Userform Name

    Darn! I knew there had to be a way. Would never have guessed to use UserForms.Add(""). : D

    Thanks Bob

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st December 2006
    Posts
    22

    Re: Subroutine That References A Dynamic Userform Name

    Quote Originally Posted by Evolvd
    Would never have guessed to use UserForms.Add("").
    Scratch that, I found some reference in the help file (finally) that says there are only three options for the userforms statement: Add, count and item, where item refers to the index number of the userform. I need to nominate the index for the userform that I was working in, rather than adding a new one. Does anyone know how to return the index number of a userform given the userforms name?

    Cheers,

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: VBA Reference Dynamic Userform Name

    I don't think Bob's method is adding a userform (despite the word add). You should be able to use it to show any form that already exists.

    On the other hand I am not sure that this approach will serve your purpose because whilst it will show any existing userform I do not think it will take the values from an active userform to the object. At least I can not see them.

    Why don't you try sending the userform as an object in the call. eg in the userform

    VB:
    Private Sub CommandButton1_Click() 
        Call UFControlsToSheet(Me) 
    End Sub 
    
    
    and in the std module

    VB:
    Public Sub UFControlsToSheet(UF As UserForm) 
        Dim i As Integer 
        For i = 1 To 2 
            Worksheets("Sheet1").Range("A" & i) = UF.Controls.Item("textbox" & i).Value 
        Next i 
    End Sub 
    
    
    Bob P is the expert on these things so maybe better to wait to see what he has to say.

    HTH
    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st December 2006
    Posts
    22

    Re: VBA Reference Dynamic Userform Name

    Thanks Carl, I didn't think of that one. That would have worked too. I also had trouble seeing the userform entries in the spreadsheet when using the add method.

    Don't worry too much about it guys, I have actually had a bit of a brain wave to make things a whole lot more simple. I now only have one userform and whenever I open it I set a cell in the spreadsheet to the number of the design I am doing, the macro controling the actions of the userform then just references this cell to determine which design I am talking about. I hope that makes sense, it has greatly reduced my headache and the size of my spreadsheet file, I just wish I had done it that way to start with... oh well, the best way to learn is through experience

    As I said before though, the main reason I posted the question was out of interest, so I am still interested if there is any other ways of doing it, but don't give yourselves a headache over it

    Cheers,
    Evolvd

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Dynamic Userform
    By salimthaj in forum EXCEL HELP
    Replies: 6
    Last Post: March 22nd, 2007, 07:41
  2. Dynamic cell reference
    By parkmaffian in forum EXCEL HELP
    Replies: 4
    Last Post: July 21st, 2006, 18:47
  3. Dynamic Userform
    By Sean Caldwell in forum EXCEL HELP
    Replies: 9
    Last Post: August 24th, 2005, 19:40
  4. reference a dynamic range
    By ricksimm in forum EXCEL HELP
    Replies: 4
    Last Post: July 22nd, 2005, 15:17
  5. Dynamic GetPivot Reference
    By dalton6275 in forum EXCEL HELP
    Replies: 1
    Last Post: January 14th, 2005, 02:17

Bookmarks

Posting Permissions

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