Quite often, when working together, a situation arises when it would be nice to know the name of the user who is currently working on the file. This will allow, for example, to greet a person by name in a dialog box or, conversely, restrict his access to data if his name is not included in the allowed list, etc.
The classic way to determine the name is to use the obvious UserName property of the Application object:
Sub Hello_1() MsgBox "Привет, " & Application.UserName End Sub
Executing such a macro will result in a simple welcome message box:
Not bad, not difficult, but there is one “but”. As UserName, the macro takes the username that is entered in the field User in Excel Options File – Options – User (File — Options — User):
The ambush is that any user on his computer can freely change this name to any other at any time. And for corporate users there, often, when installing Office, some kind of impersonal “user” is automatically registered.
A slightly more complicated, but much more reliable way, will take no Username from the Excel options, and Windows login login. It is no longer so easy to change it and, for most users, it is unique within the company’s local network.
To read it, we need to use a little-known VBA function About, which lists information about various operating system and Office settings on the current computer. The USERNAME argument will cause this function to read the Windows login of the user currently logged into the computer:
Sub Hello_1() MsgBox "Hello " & Environ("USERNAME") End Sub Thus, it is easy to organize, using a simple macro, a kind of protection when opening a file – if the book is opened by the wrong person, then certain sheets with private information or intermediate calculations are not shown to him. If you open the Visual Basic Editor (Alt + F11) and then double-click to open the module in the upper left corner ThisWorkbook, then you can insert a macro for handling the book opening event, which will perform this protection function:
Private Sub Workbook_Open() If Environ("USERNAME") <> "Nikolay" Then 'if user login is not Nikolay Worksheets("Sheet1").Visible = False 'hide Sheet1 Worksheets(3).Visible = xlVeryHidden 'do the 3rd sheet superhidden Else For i = 1 To Worksheets.Count 'otherwise Worksheets(i).Visible = True 'loop through all sheets Next i 'and make them visible End If End Sub Now all sheets will be visible only to the user with the name (login) Nikolay, and for all the rest of the three sheets of the book, only the second one will be visible. The scope for using this function is wide – use it 😉
- How to make a sheet in an excel workbook super hidden
- What are macros, where and how to insert macro code in VBA