Error running second time

[dickep]

OK, either I am doing something wrong or have a setting incorrect but I get an error if I run my VBA a second time.

Now, I REMOVE the UserForm1 from the VB editor area (under Forms). Then try to run this a second (or more) time.

The error I get is "Run-time error "75":
Path/File access error"

If you Debug, it goes to the .Properties("Name") line.

Can't you delete the Form name and have it "released"???

Anyway, I have attached the file if you want to see the error (0r not see it if you have some setting different than I do but not sure what it could be).

Thanks for your help.

E

[Bob Phillips]

Why are you trying to add forms dynamically, why not pre-define them?

[dickep]

I am dynamically making the form because the data is going to be dynamic.

I will have different fields for different areas, but those areas are not defined and as such can be somewhat freeform.

I know, this is not good, but it is what is requested.

So, if I can do this dynamically, then I can stop the multitudes of changes that are required of us right now (about 6 weeks behind as of this week and no relief in sight).

So, my request is still not answered.

Thanks
E

[lucas]

In the vba world Bob is among the best......you should ask him why he asked the question in post #2 instead of pushing your request..........he may have a very good reason for asking.

[Bob Phillips]

My thoughts were basically along three lines.

The first is that dynamic adding foms and controls is not simple, and is not robust in my experience, so it is much better to have pre-defined forms and controls, and hide and show as appropriate.

If the number is so variable that this idea is not feasible, there is a way of creating old style dialog boxes on the fly which is much simpler, much more robust.

These dialogs are predicated on lots of similar controls, and probably becomes just as diificult is there are many different types of controls. If this is the case then, IMO, the design is over-engineered and you should go back and look for a simpler solution. Most times, in VBA, complexity is not necessary and not desirable.

[dickep]

Thanks all for your thoughts and insights.

So, xld, let me think through what your proposal is.

You are basicallly stating that a UserForm be statically set. Now, if I need to have dynamic data displayed (the data on the spreadsheet will change both in "Range" and in number of "Ranges"), that could be done programatically. Am I reading this correctly?

I would just need to set the UserForm to some size that could accomodate the various comboboxes, labels, buttons, etc. that could come up. Yes, I could adjust the spacing for viewablily and that would be OK.

So, keep me in tune of what could/coud not be done in this scenario.

I will have to weigh the advantages/disadvantages against what I have been asked to come up with.

My first thoughts were everything dynamic, but after what you said, some further mulling, maybe I am over complicating some of this.

Thanks again for your help and look forward to more insights and help.

E

[Bob Phillips]

Yes that is what I am saying. As long as the level of flexibility is known, a max number of comboboxes, textboxes etc., it i better IMO to design a form with all and hide and show as appropriate. The controls can be placed dynamically, the forkm can be sized dynamicvally, all much simpler than adding controls on the fly (not to mention event code).

[chat163]

seems this macro can run on my computer, see attachment.

but when i remove form1,then re run ,the same error are happened.

[chat163]

new finding:
try this code, just create a different name for every run


[VBA]
Sub GetOption2()
Dim TEMPFORM As Object
Dim mFormName As String
Dim i As Integer
i = Int(Rnd() * 100)
'mFormName = "Form1"
' Hide VBE window to prevent screen flashing
'Application.VBE.MainWindow.Visible = False
' Create the UserForm
Set TEMPFORM = ThisWorkbook.VBProject.VBComponents.Add(3)
'TEMPFORM.Properties("Name") = ""
With TEMPFORM
.Properties("Name") = "Form" & i
.Properties("Width") = 300
.Properties("Height") = 400
'.Properties("Name") = mFormName
End With

'UserForm1.Caption = "Form1"
' Show the form
VBA.UserForms.Add(TEMPFORM.Name).Show
End Sub
[/VBA]

[mdmackillop]

Hi Chat,
When you post code, Select it and click the VBA button to format is as shown.
Regards
MD

[chat163]

Hi Chat,
When you post code, Select it and click the VBA button to format is as shown.
Regards
MD

Thanks, i'll do it later