Remove and create Userform keeping same name - Memory name conflict

[aferrer]

Dear all,
After learning so much from other members, now its time to write my first post in the forum.
I am creating an Excel file that, on the main page, shows a Userform (Userform1) if a cell of column "F" is clicked and there is data in the same row of column "B".
This form launches a second Userform (Userform_Test) showing CommandButtons for the user.

There is a data base sheet gathering the captions to create the CommandButtons for the Userform_Test.
If there is a change in the data base, the module called Create_Userform_Test removes the current Userform_Test and creates a new one.
For the new form created, I need to keep its original name as the form is called from Userform1 but and it is expected to be called from other forms.

The module Create_Userform_Test works fine if Userform_Test does not exist or if Userform_Test has not been used previously since Excel is launched.
But, once I've used Useform_Test, it seems like it keeps its name in the memory and vba shows an error name conflict (50135) when trying to name the new form.
Actually, I think that the initial Userform_Test it is not removed because it keeps active or not properly unload.

This problem is driving me crazy...

Any idea about it?


Thanks for your help

[romperstomper]

Why are you deleting the form rather than just adjusting its properties/controls at run time? The latter is far easier and not subject to the user's VBProject access security settings.

[aferrer]

Rory, the form (Userform_Test) must be updated if there is a change (add new values, delete or change existing ones...) in the data base worksheet.
My approach was to completely remove the form and create a new one when a change occurs, in that way I avoid keeping track of the kind of change: a new line added, an existing line removed, a modification in an existing line...
I just get rid of the complete form an create a new one from scratch.

Following your advice, I've tried to avoid removing the form. However I still need to remove CommandButtons as the buttons have to be updated according to the change in the data base...
I get an execution time error when I attempt to remove the CommandButtons.

Please Login or Register  to view this content.

Find attached Excel sheet.

Any hint to tackle this issue?
Thanks!

[romperstomper]

If possible, I suggest you only add the commandbuttons at run time so that you don't need to alter the VBComponent itself at all.

[aferrer]

I need to update the number of commandbuttons contained in the form based on the data base content.
For instance, I don't want a commandbutton to appear in the form if the correspondent line is deleted in the data base.
In the same way, I need to add a new commandbutton if a new line is added to the data base or to modify the caption of a commandbutton if certain line is modified in the data base.

How could I do this at run time without altering the VBComponent?
Thanks!

[romperstomper]

You add them in the Initialize event and then use a class to handle the clicking of the buttons, since they all do the same thing. Here's a basic example.

[aferrer]

Rorya, that worked great!!
Thank you

[romperstomper]

Glad to help.