VBA Express : Excel - Identifying the username (person logged onto computer)

Excel

Identifying the username (person logged onto computer)

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

mdewis

Description:

Rather than protecting spreadsheets with passwords, you can use the username object to determine if the person currently logged in (who already had to provide their Windows Password at login) can access the unprotect sheets area. Until that time, the unprotect sheets menu item is grayed out and not accessible. Referencing a permission table via a lookup will determine whether the logged in user has sufficient rights to unprotect the sheet and alter it.

Discussion:

This will allow you to identify who is logged in. The reason this is neat is b/c rather than getting into protecting worksheets with numerous passwords that need to be logged somewhere, you can just reference the username to a permissions table (another excel file) and deem whether or not the user can access protected areas based on who they are logged in as.

Code:

instructions for use

			 ' do an open workbook event procedure that disables the protection menu
Option Explicit 
 
Private Sub Workbook_Open() 
     
    Application.CommandBars("Protection").Enabled = False 
     
End Sub 
 
 
 ' Then in your actual module macro:
 
Sub User() 
     
    Dim objNet 
     
    Set objNet = CreateObject("WScript.NetWork") 
     
    MsgBox objNet.UserName 
     
     'Then you can do something like
     'if objnet.username = "God" then
     
     'Application.CommandBars("Protection").Enabled = True
     
     'End If
     
End Sub

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code from above named Sub User() to the Code Window.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Tools | Macro | Macros...
Select User and press Run.

Sample File:

ObjnetUser.zip 5.28KB

Approved by mdmackillop

This entry has been viewed 384 times.