Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Problems creating a Userform with VBA - name assignment.

This is a discussion on Problems creating a Userform with VBA - name assignment. within the Excel Questions forums, part of the Question Forums category; Hi.... I'm running Excel 2010. I need to create a Userform using VBA (which I can do OK), but I'd ...

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    God's Own County (Bingley, West Yorkshire, UK)
    Posts
    51

    Default Problems creating a Userform with VBA - name assignment.

    Hi....
    I'm running Excel 2010.

    I need to create a Userform using VBA (which I can do OK), but I'd like it to be called something other than "Userform1"

    The following code extract works ONCE only:-
    (and I have "Microsoft Visual Basic for Applications Extensibility 5.3" and "Microsoft Forms 2.0 Object Library" checked in the References)
    Code:
    Dim strFormName             As String
    Dim vbcUserForm             As VBComponent
    '*
    '** This is to stop screen flashing while creating form
    '*
      Application.VBE.MainWindow.Visible = False
    '*
    '** Create the Userform
    '*
      strFormName = "FerretStrangler"
      Set vbcUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
      vbcUserForm.Name = strFormName
    However, if I delete the Userform and repeat the code in the same session of Excel (e.g. step through it, find an error later on, fix the error, step through again) I get a "Run-time error '75' - Path/File error".

    I've looked for other methods of creating a Userform with a specific name, but to no avail.

    I've also noticed that it appears that the "Set" command creates "Userform1", but doesn't seem to have a "with name" operand.

    I can't be the only guy trying to develop code which has a VBA created Userform with a specific name in it, and I can't believe that shutting down Excel after each "false start" is trhe approved method of clearing this bug.

    I know I'm missing something (insight, skill, experience etc. to name but a few), but I'm slowly going nuts trying to fix this.

    Can any kind soul help me? Or am I beyond help?

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Posts
    416

    Default Re: Problems creating a Userform with VBA - name assignment.

    Hello, maybe so:
    Call ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

  3. #3
    Board Regular
    Join Date
    Apr 2012
    Posts
    416

    Default Re: Problems creating a Userform with VBA - name assignment.

    Sorry, my suggestion doesn't help

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Posts
    416

    Default Re: Problems creating a Userform with VBA - name assignment.


  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,935

    Default Re: Problems creating a Userform with VBA - name assignment.

    If you are creating the userform at runtime does the name really matter?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Sep 2008
    Location
    God's Own County (Bingley, West Yorkshire, UK)
    Posts
    51

    Default Re: Problems creating a Userform with VBA - name assignment.

    I'm assigning a name to make the form easier to track. My Users have options to create a variable number of forms from a large "pool" of options, and each form may have a variable number of controls on it.
    It would be easier to name the forms in a meaningful way, 'cos depending on the sequence in which the User creates them, Userform1 could be "ThisForm", "ThatForm" or "TheOtherForm", but next time Userform1 could be "ADifferentForm".
    It's also complicated by the User's ability to create a Userform, then go back to it later on in a sort of "review" process.

  7. #7
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    1,047

    Default Re: Problems creating a Userform with VBA - name assignment.

    That sounds like something that's going to be really horrible to maintain

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,935

    Default Re: Problems creating a Userform with VBA - name assignment.

    You're allowing users to create their own forms, that definitely sounds hard to maintain.

    How many forms are going to be created?

    How many of them are temporary?
    If posting code please use code tags.

  9. #9
    Board Regular
    Join Date
    Sep 2008
    Location
    God's Own County (Bingley, West Yorkshire, UK)
    Posts
    51

    Default Re: Problems creating a Userform with VBA - name assignment.

    It sounds complex, but in reality "behind the scenes" I have a Master Workbook in which each Worksheet "represents" (i.e. is tha data source for) a Userform, and the values in that Worksheet will determin the types and numbers of controls on each Userform.
    The User effectively selects one, many, or no Worksheet name from a simple listbox containing the names of all of the Worksheets in the Master Workbook, and it's after after that I start with the naming problems
    I'm trying to write parameter driven VBA such that once the process of "show a Worksheet based Userform" is written, when the moderately stable Master Workbook gets updated by the addition of another Worksheet, I don't have to do any more coding.
    I want to rename the Userform to match the Worksheet name on the Master Worksheet, so I can maintain a consistent naming convention - Worksheet "wksFred" generates Userform "ufoFred" with controls of "lbxFred1", "lbxFred2" for listboxes, "labFred1", "labFred2" for labels etc., all of which hangs off of the "simple" requirement to rename a Userform.
    I accept that this may or may not be a sensible thing to do, but that doesn't detract from the weirdness of the "rename" code appearing to work once, then fail on subsequent iterations!

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,935

    Default Re: Problems creating a Userform with VBA - name assignment.

    Why would you delete the form?
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com