Custom Ribbon based on user

[Rip1971]

Hi there,

I have made an AddIn for me and mij coworkers. We do share al lot of the buttons in de AddIn but would like te have our own specific buttons activated/deactivated based on who has the excel file open.

is this possible and if so who can provide me with the code for this.

best regards

Rip

 

[Domenic]

I would suggest that you create a custom tab for each user that includes their respective buttons so that when a user opens Excel only his/her tab becomes visible. If this is acceptable, try the following...

XML Code

1) Create a custom tab for each user, and include their specific buttons on their respective tabs.

2) Name the id for each tab after the Excel user name of each respective user. (The user name for a user can be found in Excel >> File >> Options >> General >> User name.)

3) Include a callback for the getVisible attribute for each tab.

So, let's assume that you have three users, and that their user names are John, Jane, and Robert. Your XML code would look something like this...

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="John" label="John's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="Jane" label="Jane's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="Robert" label="Robert's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
        </tabs>
    </ribbon>
</customUI>

VBA Callback

In the Visual Basic Editor, add the following code to your VBA callbacks...

'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)

    returnedVal = (control.ID = Application.userName)

End Sub

Hope this helps!

 

[Rip1971]

I would suggest that you create a custom tab for each user that includes their respective buttons so that when a user opens Excel only his/her tab becomes visible. If this is acceptable, try the following...

XML Code

1) Create a custom tab for each user, and include their specific buttons on their respective tabs.

2) Name the id for each tab after the Excel user name of each respective user. (The user name for a user can be found in Excel >> File >> Options >> General >> User name.)

3) Include a callback for the getVisible attribute for each tab.

So, let's assume that you have three users, and that their user names are John, Jane, and Robert. Your XML code would look something like this...

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="John" label="John's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="Jane" label="Jane's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="Robert" label="Robert's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
        </tabs>
    </ribbon>
</customUI>

VBA Callback

In the Visual Basic Editor, add the following code to your VBA callbacks...

'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)

    returnedVal = (control.ID = Application.userName)

End Sub

Hope this helps!

Hi

The custom tab is perfect to be used in this manner. so i will set this up.
the user name however is hard to use it gives errors in my xml. username is setup as follow "Van Winkel, Rip (R)"

 

[Domenic]

Here are a couple of options.

Option 1

In your XML code, replace any spaces in the id with an underscore, for example...

id="Van_Winkel,_Rip_(R)"

Then for your VBA callback, change it to...

returnedVal = (Replace(control.ID, "_", " ") = Application.userName)

Option 2

First, set up a lookup table where you assign a user id number, along with the corresponding user name. Let's say that you set it up in Sheet1 in your add-in, and that you have the following table (note that an id cannot start with a digit)...

User ID	User Name
A12345	John Smith
A67890	Jane Doe
etc…

Then, in your XML code, specify the user id accordingly...

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="A12345" label="John's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="A67890" label="Jane's Stuff" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
        </tabs>
    </ribbon>
</customUI>

Then for your VBA callback...

'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)

    Dim lookupReturnValue As Variant
    lookupReturnValue = Application.VLookup(control.ID, ThisWorkbook.Worksheets("Sheet1").Range("A:B"), 2, 0)
   
    If IsError(lookupReturnValue) Then
        lookupReturnValue = ""
    End If

    returnedVal = (lookupReturnValue = Application.userName)

End Sub

Hope this helps!

 

[Rip1971]

Here are a couple of options.

Option 1

In your XML code, replace any spaces in the id with an underscore, for example...

This option does not work, it gives an error in my XML code because there is a comma "," in my userid

Option 2

First, set up a lookup table where you assign a user id number, along with the corresponding user name. Let's say that you set it up in Sheet1 in your add-in, and that you have the following table (note that an id cannot start with a digit)...

This option i would like to have tried but i do not know how the get a sheet in my AddIn file if i open the .xlam file which forms my AddIn there is no sheets in there.

 

[Domenic]

This option does not work, it gives an error in my XML code because there is a comma "," in my userid

For the comma, instead of using an underscore as the placeholder, simply use another character. Then, in your VBA code, replace that character with a comma.

This option i would like to have tried but i do not know how the get a sheet in my AddIn file if i open the .xlam file which forms my AddIn there is no sheets in there.

In the Visual Basic Editor under Project Explorer (Ctrl-R), click/select ThisWorkbook for your add-in. Then, under the Properties window (F4), set the IsAddin property to False. Then, make your changes, change IsAddin back to false, and save.

 

[Rip1971]

For the comma, instead of using an underscore as the placeholder, simply use another character. Then, in your VBA code, replace that character with a comma.

In the Visual Basic Editor under Project Explorer (Ctrl-R), click/select ThisWorkbook for your add-in. Then, under the Properties window (F4), set the IsAddin property to False. Then, make your changes, change IsAddin back to false, and save.

Thank you very much, I am now working on option 2. it looks like it is working need to do some more testing but definitly on the right track

 

[Domenic]

That's great to hear, I'm glad I could help.

Cheers!

 

[Rip1971]

That's great to hear, I'm glad I could help.

Cheers!

I have tested it with different user and i found 1 problem. i have below table in my addin file. i i thought that if multiple users wanted to use a tab in the toolbar i could do that. however it will only work for the first user listed. How can i set it up that multiple users can use their Tab?
I also stil have issue with using the name but i have changed the setting in excel so it uses the userID in excel instead of name, is a good workaround for now

Departement	User ID	Name
Site	U371317	van Winkel, Rip (R)
Site	UA31361	Corbijn-Murre, Anique M (A)
Site	ND31276	van Hese, Mendy (M)
EOPlant	U371317	van Winkel, Rip (R)
EOPlant	U762761	van der Giessen, Niels (N)
PolyUrethane	U371317	van Winkel, Rip (R)
SiteLogistic	U371317	van Winkel, Rip (R)
SiteLogistic	UA29670	Stevens, Floor (F)
POPlant	U371317	van Winkel, Rip (R)
LHC	U371317	van Winkel, Rip (R)

 

[Domenic]

Okay, in that case, set up your table so that you list your user names in Column A, along with their corresponding tabs. So, for example, let's say that some users will be given access to Tab1 (which we are going to assign id number tab1), and other users will be given access to Tab2 (which we are going to assign id number tab2). So now we would have the following lookup table...

Name	Tab ID
Van Winkel, Rip (R)	tab1
Corbijn-Murre, Anique M (A)	tab1
Van Hese, Mendy (M)	tab1
Van der Giessen, Niels (N)	tab2
Stevens, Floor (F)	tab2
etc…

Note, you can of course assign a more description name for the tab, along with the id. Then you would have the following...

XML Code

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="tab1" label="Tab1" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
            <tab id="tab2" label="Tab2" getVisible="getVisible">
                  'etc
                  '
                  '
            </tab>
        </tabs>
    </ribbon>
</customUI>

VBA Callback

'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)

    Dim tabID As Variant
    tabID = Application.VLookup(Application.userName, ThisWorkbook.Worksheets("Sheet1").Range("A:B"), 2, 0)
    
    If IsError(tabID) Then
        tabID = ""
    End If

    returnedVal = (control.ID = tabID)

End Sub