Excel

Topic

New Reply

I have a workbook with a worksheet for each employee (20 in total) I am trying to write a module which hides all worksheet tabs, shows a dialog box asking for a username and password and then makes only the relevant worksheet available. Could anyone suggest a way of doing this? I am going round in circles and not getting anywhere.

Reply | Quote

Replies

I have a similar need, but the security may not be enough for you as any user could go Tools/Options/Show Tabs to navigate to other sheets. (This purely prevents them accidentally entering expense claims onto the wrong sheet.)
It also depends on the user being correctly logged in with the Application User data being correct.
This goes in the ‘ThisWorkbook’ sheet in VB:

Private Sub Workbook_Open()
un = Left$(Application.UserName, 3)
If un = “Lei” Then Worksheets(3).Activate: ActiveWindow.DisplayWorkbookTabs = True
If un = “Ann” Then Worksheets(4).Activate: ActiveWindow.DisplayWorkbookTabs = False
If un = “Pau” Then Worksheets(2).Activate: ActiveWindow.DisplayWorkbookTabs = False
If un = “Tin” Then Worksheets(1).Activate: ActiveWindow.DisplayWorkbookTabs = False
If un = “Sal” Then Worksheets(5).Activate: ActiveWindow.DisplayWorkbookTabs = False
End Sub

Reply | Quote

In Excel 97, you would need to do something along the lines of:
1) Hide all of the sheets. If you want to hide them properly, you need to do it programmatically, eg
for each s in thisworkbook.sheets
s.visible=xlSheetVeryHidden
next
Then the sheets can’t be unhidden from the format column. They can only be unhidden by code.

2) Put a password on your workbook and on your code.

3) Then, to open the workbook, you need something like:
sub auto_open()
HideAllSheets ‘Which is the code from 1)
Userform1.show
end sub

Where UserForm1 is your dialog box.

4) On UserForm1 you need to insert a username and password field, and an ok and cancel button.

5) The ok button obviously needs to check that the password is correct, and if so, then unhide the correct sheet, eg
thisworkbook.sheets(application.username).visible = xlsheetvisible

Is that enough to help you?

Jon

Reply | Quote