CODEWITHSUNDEEP
excelvbafilterautofilter
T_M_
T_M_

Reputation: 21

Filter based on username excel

Hi not sure if this will be doable via vba in excel or whichever way. I would like to do an auto filter by username. I have a file which different users will use, it's not shared so that only one person can use it at a time. Would it be possible for excel to recognise the username and filter column X to that user name. to display the rows relevant to that user.

ie Column X
John Doe
John Smith
Jane SMith

If john Smith opens the file, it will auto filter to show only john smiths rows, if the user name isn't in column X, show all?

This isn't security conscious, so if someone unfilters, it's not a problem, just a nice to have.

I know excel can call up user names as I have some code that send's a file and saved within the file name includes the username using

Environ("Username"

any help would be much appreciated as I dont even know where to start with this

thanks

Upvotes: 1

Views: 2830

Answers (1)

Wolfie
Wolfie

Reputation: 30156

Let's say you had the below dummy data in Sheet1:

data

We want to filter on Name, column 3.

Example code to achieve this would be:

Sub NameFilter()
    Dim name As String
    name = Environ("Username") 'e.g. "Mary"

    ThisWorkbook.Sheets("Sheet1").Range("A1:C6").AutoFilter 3, "=" & name
End Sub

You could place this in the Workbook_Open function to trigger when the workbook is opened:

Workbook Open

We can make this a bit more robust and flexible, also accounting for your requirement to leave unfiltered if the name isn't present. See my comments below for details:

Sub NameFilter()
    ' Get the username for filtering
    Dim name As String
    name = Environ("Username")
    ' Define the filter range
    Dim Rng As Range
    Set Rng = ThisWorkbook.Sheets("Sheet1").Range("A1:C6")
    ' Define the column for filtering
    Dim FiltColumn As Long
    FiltColumn = 3
    ' Clear filters by default
    Rng.AutoFilter FiltColumn
    ' Check if the name exists
    NameExists = Application.Match(name, Rng.Columns(FiltColumn))
    ' Filter if the name does exist
    If Not IsError(NameExists) Then
        Rng.AutoFilter FiltColumn, "=" & name
    End If
End Sub

Upvotes: 2

Related Questions