VBA代码通过Windows自动筛选数据登录用户ID？ [英] VBA code to Filter data automatically by windows log-in user ID?
问题描述
EX：Excel数据：
Id部门规模
1北IT 8.5
2南方财务8.0
3北财务8.0
4西IT 8.5
5东方财经8.0
6南IT 8.5
现在我有一个情况是：
如果来自 North 的一个用户运行VBA宏，则excel结果应仅显示分区北。如果来自 South 的用户运行VBA宏，则excel结果应仅显示 Division South。
EX：如果一个用户从 South 运行VBA宏，结果如
Id部门规模
2南方财务8.0
6南IT 8.5
如何通过分区设置用户过滤VBA中的数据。
I用户数量有限，主要是4-6位用户。有没有办法使用他们的Windows凭据过滤Excel中的数据，通过在VBA中添加一些代码？
任何帮助？
由于您拥有少量用户，您可以在VBA项目中直接对其进行硬编码。为此，我将使用 Dictionary 对象。字典是将数据存储在一个键值中的一种方式。
我们来看看一些代码。
Sub Test（）
Dim dict As Object
Dim key As Variant
Dim user As String
Dim region As String
设置dict = CreateObject（Scripting.Dictionary）
'将您的用户名/区域
'对添加到字典对象
dict.Add user1，South
dict.Adduser2，North
dict.Adduser3，South
dict.Adduser4，West
'.. etc
'获取当前登录的用户名
user = Environ（username）
'循环通过字典找到符合用户名
的
'区域对于每个键在dict.Keys
如果key = user然后
region = dict.Item（key）
结束如果
下一个键
'如果没有找到用户名，我们应该
'exit th e子程序。您可以显示
'一个消息框或类似的
如果region = vbNullString然后
MsgBox无效的用户名！，vbCritical，无效的用户名
退出子
结束如果
'从这里出发，你会像
一样做，通过变量
'区域作为你的一个参数
End Sub
如果您不希望用户看到其他用户被分配到哪些区域您应该密码保护您的VBA代码。您可以通过转到工具 - > VBAProject属性... - >保护并勾选锁定项目查看框并输入密码。
我应该补充说，使用这个字典方法有局限性。如果一个用户需要分配到多个区域，例如，这不会像每个键一样工作，值对必须是唯一的。
如果是这样，我将研究处理这个服务器端。您可以创建一个新的sql表，其中包含用户名，区域对。然后，您可以将用户名作为参数传入SQL存储过程，并使用该参数来控制过程返回的结果。这最终可能更可取。
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?
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.
这篇关于VBA代码通过Windows自动筛选数据登录用户ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
