Same form opened multiple times

Hi,

I am programming a simple database in excel that allows a user to select a part and a material from 2 comboboxes, and then have a userform pop-up and show him the material's properties.

There is a "masterform", i.e. Form1, where the user makes the selections. There are 11 different SubForms for the different parts.

Ex.
Masterform
User selects a Part from combobox1 (Part_x).
User selects a Material from combobox2 (Material_y).

*SubformPart_x pops up filled with the properties associated with selected Material_y.

What I want, is to keep the "masterform" active so the user can select different materials for comparison. The option ShowModal=False does this perfect.

Problem
However, and here is the problem, if the user selects twice the same part, the Subform associated with that part cannot be displayed twice (or more) at the same time.

The problem is that the different fields in the subform are filled by looking into different sheets in the workbook. I figure that when I select a new material, but for the same part (thus filling the same userform), the different fields are already filled. To make it work, I need to close the subform and then select a material.

If I select materials from 2 different parts (using two different subforms), there are no problems at all.

Question
Is there a way to unload or clear variables from a form, but keep it opened ? What I mean is that when I select a material and the associated subform opens, is it possible to clear the variables filling the different textboxes, but keep the subform opened with the textboxes filled, so that when I select another material (same part), a new subform pops up with the new material properties.

Ex.
Masterform
User selects a Part from combobox1 (Part_x).
User selects a Material from combobox2 (Material_y).

*SubformPart_x pops up filled with the properties associated with selected Material_y.

User selects the same Part from combobox1 (Part_x).
User selects a new Material from combobox2 (Material_y2).

*SubformPart_x pops up filled with the properties associated with selected Material_y2, with the previous SubfromPart_x filled with the properties associated with Material_y.

i.e. There would be 3 userforms opened at the same time : Masterform, SubformPart_x with Material_y properties & SubformPart_x with Material_y2 properties.


Would the best way to make this work be to create a Public variable that would be changed everytime a user selects a new material ?

Thanks in advance,
Alexis

From reading more deeply into the subject, I need to create the new subform as a new object in order for it to be considered a different userform.

Please Login or Register  to view this content.

Anyone have any other way to do this ?

The example code below creates a new copy of UserForm1 and sets some example properties on the new copy.

Please Login or Register  to view this content.