Tool bar button than can open different UserForms

[ABabeNChrist]

Here’s what I have and what I’m trying to achieve. I have a workbook with 20 different sheets; all the sheets are different and are used to generate different types of reports. Each sheet has its own UserForm or (s) that performs a special feature for that selected sheet. What I’m trying to achieve is, by using just one button that whatever sheet is active/visible it would then refer to a selected UserForm for that preferred sheet. I also created a custom toolbar with the use of UI Editor that I was thinking of adding this button too. I’m trying to eliminate buttons on the sheet. I know I can create an additional UserForm to give me a selection to other UserForms but I was hoping for something different….

[StuartR]

...Each sheet has its own UserForm...

How do you know which UserForm is associated with each sheet?

It would be very easy to create a button that displayed a different userform depending on the Active Worksheet, but you would need some way to associate each worksheet with a specific userform.

[ABabeNChrist]

If the sheet is named Bedroom then the UserForm is named BedroomHide
All I did was use the sheet name and added the word Hide at the end.

[Jan Karel Pieterse]

I expect this is the best you can do:

Code: Select all

    Select Case LCase(ActiveSheet.Name)
    Case "bedrooms"
        bedroomshide.Show
    Case "kitchens"
        KitchensHide.Show
    Case Else
        'Do nothing, no form available
    End Select

[rory]

Something like:

Code: Select all

    Dim frm
    Set frm = UserForms.Add(ActiveSheet.Name & "Hide")
    frm.Show

[Jan Karel Pieterse]

userforms.add. Interesting! Add is not shown as a method of the userforms class. But it does work.

[ABabeNChrist]

Hi Jan and rory
First off thank you for your assistance

I expect this is the best you can do:

Code: Select all

    Select Case LCase(ActiveSheet.Name)
    Case "bedrooms"
        bedroomshide.Show
    Case "kitchens"
        KitchensHide.Show
    Case Else
        'Do nothing, no form available
    End Select

Jan I tried using the approach as you described but it seemed to only select Case Else.
How I came about this was, when I ran the code I would receive no errors, so then I added a MsgBox after Case Else, and then would only receive message no matter what sheet I was on.

Something like:

Code: Select all

    Dim frm
    Set frm = UserForms.Add(ActiveSheet.Name & "Hide")
    frm.Show

Rory your approach did work for most of my sheets except for a couple that have a sheet name followed by (s)
Like this “Bathroom(s)
I then realized that using (s) is not a legal recognized name for a UserForm
I thought about renaming all my sheets and removing the (s), but that would entail changing all code that refers to those sheets
Also a couple sheets use 2 words with a space between and of course that will not work with a UserForm name also.

So I thought of possibly using a If Then statement, something like this.

Code: Select all

    If ActiveSheet.Name = "Bedroom(s)" Then
        BedroomHide.Show
    End If

    If ActiveSheet.Name = "Bathroom(s)" Then
        BathroomHide.Show
    End If

    If ActiveSheet.Name <> "" Then
        MsgBox "Selection not available"
    End If

But was unsure how to have a message appear if sheet is not part of selections above

[sdckapr]

The Case if construction that Jan posts is a streamlined version of the The if.. then construction. The Case Else is when none of the listed sheets is active. With the If.. Then you must create a distinct IF for all the sheets not listed.

Thus the Case If construction is much better than the multiple if ...then.

In the comparison in VB the text strings must match EXACTLY including the case...

Steve

[ABabeNChrist]

Hi Steve
I tried using Jans approach again, making sure the sheet names and UserForms were spelled correctly
Like so

Code: Select all

    Select Case LCase(ActiveSheet.Name)
    Case "Interior"
        InteriorHide.Show
    Case "Kitchen"
        KitchenHide.Show
    Case Else
        'Do nothing, no form available
    End Select

it still wants to End Select
I was also using the tabs accross the bottom to select the correct sheet before running code

[sdckapr]

Look at your cases:
If the sheet name is "Interior"
The case is looking for "interior" (Lcase of "Interior") and thus will NEVER equal "Interior"
[Similarly "Kitchen" should be "kitchen"].....

The "Case" statements should all have lower case letters since that is what you told it to compare....

Steve

PS if you don't want ot use lowercase you can instead just use:
Select Case ActiveSheet.Name

[ABabeNChrist]

Thank you Steve
I see what you mean. I over looked the LCase
I went ahead and selected your suggestion of
Select Case ActiveSheet.Name
Because I’m so familiar with typing with the correct name (Have done it a million times lol) and keeps everything consistent

[Jan Karel Pieterse]

I almost always use either UCase or LCase in select case statements because I have had too many cases where I got the case wrong :-)