CODEWITHSUNDEEP
vbaexcel
GK1960
GK1960

Reputation: 121

VBA code to Filter data automatically by windows log-in user ID?

I got VBA code that runs sql server stored proc, brings data into excel.

EX: Excel Data:

Id     Division     Department     Scale
1      North        IT             8.5
2      South        Finance        8.0
3      North        Finance        8.0
4      West         IT             8.5
5      East         Finance        8.0
6      South        IT             8.5

Now I got a situation that is:

If one user from North runs the VBA macro, the excel result should only show Division North. If users from South runs VBA macro, the excel result should only show Division South.

EX: If one user from South runs VBA macro, the result like

Id     Division     Department     Scale
2      South        Finance        8.0
6      South        IT             8.5

How can I setup users by Division to filter data in VBA.

I got limited No. of users mostly 4-6 users. Is there any way to use their windows credentials to filter data in excel, by adding some code in VBA?

any help?

Upvotes: 0

Views: 2670

Answers (3)

Soulfire
Soulfire

Reputation: 4296

Since you have a small number of users, you can hard code this directly in your VBA project. To do this I will use the Dictionary object. A dictionary is a way to store data in a key, value pair.

Let's dive into some code.

Sub Test()

    Dim dict As Object
    Dim key As Variant
    Dim user As String
    Dim region As String

    Set dict = CreateObject("Scripting.Dictionary")

    'Add your username / region
    'pairs to the dictionary object
    dict.Add "user1", "South"
    dict.Add "user2", "North"
    dict.Add "user3", "South"
    dict.Add "user4", "West"
    '.. etc

    'Get the username of the currently logged-in person
    user = Environ("username")

    'Loop through dictionary to find the
    'region which matches the username
    For Each key In dict.Keys
        If key = user Then
            region = dict.Item(key)
        End If
    Next key

    'If the username is not found, we should
    'exit the subroutine.  You could display
    'a messagebox or something similar
    If region = vbNullString Then
        MsgBox "Invalid username!", vbCritical, "Invalid Username"
        Exit Sub
    End If

    'From hereon out you would do as you
    'normally do, passing in the variable
    'region as one of your parameters


End Sub

If you don't want users to see which regions other users are assigned to you should password protect your VBA code. You can do this by going to Tools --> VBAProject Properties... --> Protection and tick the 'Lock project for viewing' box and enter a password.

I should add that using this dictionary method has limitations. If one user needs to be assigned to multiple regions, for example, this will not work as each key, value pair must be unique.

If this is the case, I would look into handling this server side. You could create a new sql table which has username, region pairs. You could then pass in the username as a parameter to your SQL stored procedure and use that parameter to control which results are returned by the procedure. This is ultimately probably more desirable.

Upvotes: 3

shA.t
shA.t

Reputation: 16968

Your answer is just in this Answer.

I think you need a solution like this:

  • You need a data set that have at least to column like UserName and Direction that specify any users to a direction.

  • Now you have your UserName = Environ("username") that allows you to find a specific user.

  • After finding tha Direction from your data set you should filter all your Sheet by that clue like hiding rows that are not in that direction.

  • If you want to have a filter that makes the file opener that he or she can't access to the other rows, You also need to write some codes to protect that sheet of data.

Upvotes: 1

CustodianOfCode
CustodianOfCode

Reputation: 674

Yep. Environ("username") gives the windows login, and for 4-6 users you could even hard code the mapping of north and south from this, or of course set it up as a range in excel, or a database table and lookup that way.

Upvotes: 1

Related Questions