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?

 

[sts023]

The only time I need to deliberately delete the Userform is during testing. When live, the dynamically created Userforms will be deleted at the end of the session (to be tidy).
During testing I manually delete the Userform, as trying to create another one with the same name as one I created earlier gives an obvious "duplicate name" failure.

 

[sts023]

DUPLICATED REPLY - FIRST ONE DIDN'T SEEM TO LOAD
I only delete Userforms during testing to stop the "duplicate name" errors.
When Live, for tidyness the dynamically created Userforms will be deleted (probably), although it may be useful to be able to look back at previous worksheets to determin what was on a given Userform at a specific date (you can tell this project is still in the design/development phase, can't you!).

 

[Kyle123]

Why not just have a single blank form in the project, create multiple instances of it and add controls dynamically as required.

That way you'll always be able to access the form and controls and don't have to worry about naming it. You also wouldn't need to add any code at runtime - users wouldn't need higher trust settings since you wouldn't need access to the VBE. So this method would have all the same benefits (if not making things easier) without the drawbacks of making things hard to maintain, adding code at runtime and having to know the names of the controls you are adding.

 

[sts023]

It's tidier. Why not?

 

[sts023]

Kyle123....
Sonds promising, but I'm not clear on what you mean by "create multiple instances of it" (sorry if tha's a stupid observation - I'm still fairly new to Excel). Are you suggesting having a sort of basic blank Userform, and modifying its attributes etc. each time I want to create a "new" form?
If so, it would seem to cut out the "create" phase, but would I still have rename problems?

 

[RoryA]

You wouldn't rename it at all - there should be no need.

 

[Kyle123]

There's no need, keep them in a collection

Yes, have a single Blank userform - Userform1

Dim ufColl As Collection

Sub GenerateForms()
    Dim uf As UserForm1
    Dim x As Integer


    Set ufColl = New Collection
    For x = 1 To 5
        Set uf = New UserForm1
        ufColl.Add uf, "userform" & x
    Next x
    
    Set uf = ufColl("userform3")
    With uf
        .Caption = "Dynamic Form"
        With .Controls.Add("forms.combobox.1", "cbox")
            .Top = 10
            .Left = 10
            .List = Array("Something", "something Else")
        End With
        .Show
    End With
    
End Sub


Sub KillCollection()
    If Not ufColl Is Nothing Then Set ufColl = Nothing
End Sub

 

[sts023]

Thanks Kyle123! It seems like the Collection is exactly what I need.
I stuck the sample code in a new Workbook's module in it ran like a dream.
I modified the sample code very slightly to change

ufColl.Add uf, "userform" & x

to

ufColl.Add uf, "Waggle" & x

and change the subsequent reference to

  Set uf = ufColl("Waggle3")

Accepting that I don't fully understand the concept (just for a change!) of Collections, I notice that the collection doesn't seem to exist in the Explorer window as a member of the Forms group, and in the Watch window when I set a Watch on ufColl, there are plenty of references to "Userform1", but I can find no mention of any of the "Waggle"s!
(Having said that, I am registered Blind, so perhaps I've missed it somewhere!).

Thanks again to all those offering helpful replies and guidance - it just goes to show that contrary to popular belief the world does have some genuinely nice people in it!

 

[ipxi2001]

Hey'all

I had the same Problem and it took me days finding the answer:

You can use the name again if you save the workbook first.

Found at: http://www.mrexcel.com/forum/excel-questions/51400-duplicating-userform-names.html

Actually, while it works saving the Workbook manually, using the code

ThisWorkbook.Save

did not work, hence I used a trick:

Application.DisplayAlerts = False
ThisWorkbook.Sheets("any_sheet_name_in_this_workbook").Activate
Application.CommandBars.FindControl(ID:=3).Execute
Application.DisplayAlerts = True

Hope this helps others too

Cheers