activate worksheet with single password entry

[dodson]

Hi Everyone...I'm a newbie here.

Desired Scenario,

1. User opens spreadsheet.
2. Spreadsheet has two worksheets
3. User selects to view "Sheet2".
4. User is prompted for password (once for the entire file session).
5. Correct password (softball) allows access to worksheet, incorrect password returns user to Sheet1.

I can create this scenario with password entry required each time Sheet2 is selected, however I would like to avoid multiple password entries if possible.

I have attempted the code offered here - http://www.mrexcel.com/archive/VBA/1646.html - with no luck.

I currently have functional code to ensure macros are enabled to view the worksheets in question...I just need help sorting out this multiple password entry if possible.

Thanks in advance for any help offered!

 

[JoeMo]

In ThisWorkbook module:

Private Sub Workbook_Open()
With Sheets("Sheet2")
    .Range("A1").Value = 0
End With
End Sub

In Sheet2 module:

Private Sub Worksheet_Activate()
Dim rng As Range, ans As String
Set rng = Range("A1")
If rng.Value > 0 Then Exit Sub
ans = InputBox("Enter the password")
If ans <> "softball" Then
    Sheets("Sheet1").Select
Else
    rng.Value = rng.Value + 1
End If
End Sub

Cell A1 in sheet2 is used to store the number of times this sheet is activated while the workbook is opened. You can use a different cell by altering the cell address in both modules.

 

[dodson]

Thanks Joe,
That code worked flawlessly.

I have one issue with it. When Sheet2 is selected, the worksheet is visible in the background behind the password input box. For non-pwd users, I wouldn't want the information on the worksheet to be visible at all. Any suggestions how to prevent this?

 

[JoeMo]

Thanks Joe,
That code worked flawlessly.

I have one issue with it. When Sheet2 is selected, the worksheet is visible in the background behind the password input box. For non-pwd users, I wouldn't want the information on the worksheet to be visible at all. Any suggestions how to prevent this?

Amend the Sheet2 code to this:

Private Sub Worksheet_Activate()
Dim rng As Range, ans As String

Set rng = Me.Range("A1")
If rng.Value > 0 Then
    Me.Visible = xlSheetVisible
    Exit Sub
Else
    Me.Visible = xlSheetHidden
End If
ans = InputBox("Enter the password")
If ans = "" Or ans <> "softball" Then
    Sheets("Sheet1").Activate
    Me.Visible = xlSheetVisible
    Exit Sub
Else
    rng.Value = rng.Value + 1
    Me.Visible = xlSheetVisible
    Me.Activate
End If
End Sub

 

[dodson]

once again...worked flawlessly! thanks a ton!

I was also able to incorporate an "incorrect password" message myself...and force the workbook to open to Sheet1.

Slowly but surely I'm learning.

Thanks again for your time and code help!!

 

[dodson]

new problem...I have the workbook functioning as desired...now I want to protect the worksheets and the workbook to prevent users from inadvertently altering formulas.

I protected the workbook and now when I open the workbook and select Sheet2, I get the following error

Run-time error '1004':
Method 'Visible' of object '_Worksheet' failed

The 'debug' button points me to line 6 of the following code (located in the Sheet2 module):

Set rng = Me.Range("A4")
If rng.Value > 0 Then
   Me.Visible = xlSheetVisible
    Exit Sub
Else
    Me.Visible = xlSheetHidden
End If
ans = InputBox("Enter the password")
If ans = "" Or ans <> "softball" Then
    MsgBox "Incorrect Password", vbCritical, "You are not allowed to view the selected worksheet"
    Sheets("Sheet1").Activate
    Me.Visible = xlSheetVisible
    Exit Sub
Else
    rng.Value = rng.Value + 1
    Me.Visible = xlSheetVisible
    Me.Activate
End If
End Sub

Apparently the workbook protection is interfering with the worksheet code. Any ideas?

 

[dodson]

I was able to resolve the workbook protection problem...I used "protect worksheet" instead and all seems to work well.