Event Procedure for Multiple User Forms in Multiple Worksheets VBA

[wcm69]

I have created a workbook which lists Products and their Suppliers. The workbook contains 51 worksheets (plus a contents page)

I have categorised each Product into Product Ranges - of which their are 17.
For ease of use, each of the 17 Product Ranges are on 3 worksheet tabs, hence 51 worksheets.

I now have 17 User Forms which are named in accordance with the (Product Range) tab names.

What I want is for each of the 17 User Forms to be assigned or set, (i.e. active / visible) to 3 'Product Range Worksheets'. Whilst one User Form is active, the other 16 User Forms are to stay invisible / non-active until their particular 3 worksheets are open / activated.
I am using the User Forms with Command Buttons to navigate through the workbook as there are so many worksheets.

Having very limited knowledge in VBA I'm hoping the above is clear and someone can provide a code/s (with a few instruction) that will do the trick.

Thanks in advance

 

[My Aswer Is This]

This is a duplicate post on this same Forum:
See the original post here:
https://www.mrexcel.com/forum/excel...-modeless-form-visual-basic-applications.html

You will see the exact same question in post #20

 

[My Aswer Is This]

Your probable going to have to Right click on all 51 sheets and enter some code like this:

Bob being the name of the UserForm you want to open when you activate your sheet.

Private Sub Worksheet_Activate()
Bob.Show modeless
End Sub
Private Sub Worksheet_Deactivate()
Bob.Hide
End Sub

 

[Norie]

17 userforms?

Are they all different? ie different controls, layout etc

 

[wcm69]

Hi Norie,

Thanks for the reply.

All 17 User Forms are different. I want each User Form to be used on 3 Worksheets to navigate through the workbook. (I have a total of 51 worksheets)

Currently when I use the codes provided, the specified User Form opens in the correct worksheet, but it also opens the same User Form in all the other sheets in my workbook as well?

I need when I click on the sheet tab to go to another page the correct User Form becomes visible and the previous or non required User Form/s become invisible or de-activate.

If you can provide any help I'd be grateful.

Thanks to all in advance

 

[Norie]

What code do you currently have?

Is there a way to associate a particular userform with a particular worksheet(s), eg by name?