In a company environment we might wish to control what a user can and can’t see within a workbook, depending on their authority level. Windows usernames and levels are stored in a database (preferably) or, as in this case, a hidden sheet within the workbook.
This article assumes the reader has the Developer ribbon displayed, and is familiar with the VBA Editor. If not, please Google Excel Developer Tab or Excel Code Window.
- Open a blank workbook. In testing our code, Sheets 1, 2 and 3 will be visible at administrator level. Sheets 2 and 3 will be visible at manager level and Sheet 3 will visible to anyone else.
- Create a new sheet within the workbook called UserLevels and populate it as shown. Put your own Windows username in cell A2.
If you don’t know your Windows name, press the Win + r keys. Type cmd in the run input box that opens. At Command line prompt type echo %username%.
The application uses the Auto_open procedure to find who is logged onto the computer before displaying any sheets. Then it cycles through column A of the sheet above until it finds a match to the username and notes his or her level (a database would require no cycling).
Should the application not find a match, it will escape the loop and presume the level to be 3.
We’ll use the For…Next statement for looping. The workbook used in the exercise can be found here.
- In the code window, insert a new Module (“modMain”) with the following code:
Option Explicit Dim strUser As String ‘Declare variables to be used by the application Dim sRow As Integer Dim sLookupName As String Dim nLevel As Integer Dim i As Integer Function UserNameWindows() As String ‘called from code in Auto_open, below. UserNameWindows = Environ("UserName") End Function Sub Auto_Open() Sheets("Sheet1").Visible = xlVeryHidden ‘The user can’t unhide xlVeryHidden sheets. Sheets("Sheet2").Visible = xlVeryHidden Sheets("UserLevels").Visible = xlVeryHidden strUser = UserNameWindows() 'call the function For i = 2 To 102 ‘Cycle through 100 users. sLookupName = Sheets("UserLevels").Cells(i, 1) ‘Get name from column 1. nLevel = Sheets("UserLevels").Cells(i, 2) ‘Get Level from column 2. If sLookupName = "" Then ‘user not found Exit For End If If sLookupName = strUser Then ‘A match! If nLevel = 1 Then Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = True ElseIf nLevel = 2 Then Sheets("Sheet2").Visible = True End If Exit For 'Stop looking since match has been found. End If Next i End Sub Sub Auto_Close() Sheets("Sheet1").Visible = xlveryhidden Sheets("Sheet2").Visible = xlveryhidden Sheets("UserLevels").Visible = xlVeryhidden Activeworkbook.Save ‘Force a final Save after hiding sheets End Sub
- For test purposes, add four buttons to sheet3 so we can change our user-level on demand to show hidden sheets.
- Add temporary code to modMain.
Sub ChangeLevel1() Sheets("UserLevels").Range("B2") = 1 Call Auto_Open End Sub Sub ChangeLevel2() Sheets("UserLevels").Range("B2") = 2 Call Auto_Open End Sub Sub ChangeLevel3() Sheets("UserLevels").Range("B2") = 3 Call Auto_Open End Sub Sub ShowUserLevels() Sheets("UserLevels").Visible = True End Sub
- Assign each button to its relevant procedure (right_click the button, choose Assign Macro…).
- Click buttons in turn to view results.
- Protect your macro from being viewed or changed: Tools>Properties>Protection.
Repair Corrupt Excel Worksheets
As Excel grows more complex with each succeeding version, so we increasingly find file corruptions where the workbook fails to open properly. If you have an effective Excel repair tool in your armoury, you can solve such issues easily and quickly.
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar problem and sql recovery software products. For more information visit www.datanumen.com