Macro to select name in slicer

AndrewD04

New Member
Joined
Aug 24, 2017
Messages
26
Morning, Is it possible to use a macro to detect your username and then select that username in a slicer
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,046
For the username associated with the operating system, you can use the Environ function...

Code:
environ("username")
For the username associated with the application, you can use the UserName property of the Application object...

Code:
application.UserName
Hope this helps!
 

AndrewD04

New Member
Joined
Aug 24, 2017
Messages
26
For the username associated with the operating system, you can use the Environ function...

Code:
environ("username")
For the username associated with the application, you can use the UserName property of the Application object...

Code:
application.UserName
Hope this helps!
Thank you so much, do you know a way to use that username to select the person in a slicer so it displays only their data
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,046
Try the following...

Code:
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] SelectUserFromSlicer()

    [COLOR=darkblue]Dim[/COLOR] oSlicerItem         [COLOR=darkblue]As[/COLOR] SlicerItem
    [COLOR=darkblue]Dim[/COLOR] sUserName           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    sUserName = Environ("username") [COLOR=green]'or Application.UserName[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] ThisWorkbook.SlicerCaches("Slicer_User") [COLOR=green]'change the slicer name accordingly[/COLOR]
        .ClearManualFilter
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oSlicerItem = .SlicerItems(sUserName)
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oSlicerItem [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oSlicerItem [COLOR=darkblue]In[/COLOR] .SlicerItems
                [COLOR=darkblue]If[/COLOR] UCase(oSlicerItem.Name) <> UCase(sUserName) [COLOR=darkblue]Then[/COLOR]
                    oSlicerItem.Selected = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] oSlicerItem
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
Hope this helps!
 

Forum statistics

Threads
1,085,586
Messages
5,384,592
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top