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)
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?
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
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?