Problems creating a Userform with VBA - name assignment.

[sts023]

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)

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?

 

[andrewkard]

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

 

[andrewkard]

Sorry, my suggestion doesn't help

 

[andrewkard]

Try this:
SAS Fifth Dimension Tips and Techniques

 

[Norie]

If you are creating the userform at runtime does the name really matter?

 

[sts023]

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.

 

[Kyle123]

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

 

[Norie]

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?

 

[sts023]

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!

 

[Norie]

Why would you delete the form?