Custom Ribbon based on user

Rip1971

Board Regular
Joined
Nov 3, 2020
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
 
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...

NameTab 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

XML:
<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

VBA Code:
'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
Is it possible to give me as administrator all tabs visible and only the user their defined tabs. since i am the only one that need more then 1 tab. Because in this version I only get the first tab1 and not tab2 ..tab 3
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Since you're the only one that will have access to all tabs, you can simply add an If statement that checks whether the current user is the administrator. If so, it sets the returnedVal to True, and then it exits the sub. Otherwise, it proceeds to lookup the tab id from the lookup table for the current user. So the code would be amended as follows...

VBA Code:
'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)
    
    Dim administratorName As String
    administratorName = "Smith, John" 'change the name accordingly
    
    Dim currentUserName As String
    currentUserName = Application.username
    
    If currentUserName = administratorName Then
        returnedVal = True
        Exit Sub
    End If

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

    returnedVal = (control.ID = tabID)

End Sub
 
Upvote 0
Solution
Since you're the only one that will have access to all tabs, you can simply add an If statement that checks whether the current user is the administrator. If so, it sets the returnedVal to True, and then it exits the sub. Otherwise, it proceeds to lookup the tab id from the lookup table for the current user. So the code would be amended as follows...

VBA Code:
'Callback for getVisible
Sub getVisible(control As IRibbonControl, ByRef returnedVal)
   
    Dim administratorName As String
    administratorName = "Smith, John" 'change the name accordingly
   
    Dim currentUserName As String
    currentUserName = Application.username
   
    If currentUserName = administratorName Then
        returnedVal = True
        Exit Sub
    End If

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

    returnedVal = (control.ID = tabID)

End Sub
Thank you very much. this is just what i needed. it works like a charm.
 
Upvote 0
You're very welcome, and thanks for the feedback.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top