Hi guys,

I'd like to implement a simple multi-password access to the book just after
the book opens. It's a slight variation to the "Password to open" in the
"Save Options" dialog, which allows only a single password.

The book should always open to a sheet named: Table of Contents, with a
prompt to the user to enter a password, and click OK. The other button is
"Cancel" which closes the book.

The password entered will be matched against a list of authorised passwords
input in col A (in A1 down) in a (very?)hidden sheet. If the password
entered finds a match, then access to the book will be as per normal.

Appreciate any help / views. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

you answered the question already. Use a Very Hidden sheet. Hidden sheets can
be viewed via menu items, but not a Very Hidden sheet.
Use a userform to get the password ... neater than an input box. then simply
use a MATCH() function. If this errors then there's no match. USe a counter
to allow the user three attempts(say) before closing the workbook.
in the close event set Thisworkbook.Saved = True which means that the user
doesn't get the chance to save th eworkbook.



"Max" wrote:

Hi guys,

I'd like to implement a simple multi-password access to the book just after
the book opens. It's a slight variation to the "Password to open" in the
"Save Options" dialog, which allows only a single password.

The book should always open to a sheet named: Table of Contents, with a
prompt to the user to enter a password, and click OK. The other button is
"Cancel" which closes the book.

The password entered will be matched against a list of authorised passwords
input in col A (in A1 down) in a (very?)hidden sheet. If the password
entered finds a match, then access to the book will be as per normal.

Appreciate any help / views. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks, Patrick. Could I have some sample code to help me proceed ? Just
something simple will do. I'm still pretty basic in vba. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Patrick Molloy" wrote in message
...
you answered the question already. Use a Very Hidden sheet. Hidden sheets
can
be viewed via menu items, but not a Very Hidden sheet.
Use a userform to get the password ... neater than an input box. then
simply
use a MATCH() function. If this errors then there's no match. USe a
counter
to allow the user three attempts(say) before closing the workbook.
in the close event set Thisworkbook.Saved = True which means that the user
doesn't get the chance to save th eworkbook.

Hi Max,

The following is off the top of my head and is just
to give you some idea of how to start. I can almost
guarantee it won't work.<g

Regards,
Jim Cone
San Francisco, USA
'----
1. Hide all sheets when the workbook closes and
make the intro sheet visible.
Something like this in the ThisWorkbook Module...
'-----------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
ActiveWorkbook.Unprotect ("ISurrender")
For Each wks In ActiveWorkbook.Worksheets
If wks.Name < "Intro" then
wks.Visible = xlVeryHidden
End If
Next wks
With ActiveWorkbook
.Protect Password:="ISurrender"
.Save
End With
End Sub
'---------------------------

2. Place a text box and a command button on the sheet.
(from the control toolbox)
Freeze panes to keep them both in view.
The command button title could be...
"Enter password below then click me"
Set the textbox password characters to *.

3. Enter code something like the following in the "Intro"
sheet code module...
'------------------------------
Public lngCount As Long

Private Sub CommandButton1_Click()
Dim strPW As String
Dim varPassWds As Variant
Dim i As Long

varPassWds = Array("Send", "In", "The", "Marines", "ISurrender")
strPW = Me.TextBox1.Text
TryAgain:
For i = 0 To 4
If strPW = varPassWds(i) Then
'run code to show all the sheets and hide the intro sheet
Exit Sub
End If
Next

If lngCount < 4 Then
lngCount = lngCount + 1
MsgBox "Please try again. "
Else
MsgBox "Please see your supervisor for access. "
ThisWorkbook.Close savechanges:=False
End If

End Sub

'------------------------------

"Max" wrote in message
...
Thanks, Patrick. Could I have some sample code to help me proceed ? Just
something simple will do. I'm still pretty basic in vba. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Patrick Molloy" wrote in message
...
you answered the question already. Use a Very Hidden sheet. Hidden sheets
can
be viewed via menu items, but not a Very Hidden sheet.
Use a userform to get the password ... neater than an input box. then
simply
use a MATCH() function. If this errors then there's no match. USe a
counter
to allow the user three attempts(say) before closing the workbook.
in the close event set Thisworkbook.Saved = True which means that the user
doesn't get the chance to save th eworkbook.

Jim, many thanks for that ! And I've just received some sample code from
Patrick to play with as well. Many thanks to Patrick, too. Wonderful !
Let me work on both your contributions over the next couple of days and see
whether I can make any headway here <g. I'll post back further in this
thread.
Have a great day !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Jim,

Could you drop me some code for use in the commented line below?

If strPW = varPassWds(i) Then
'run code to show all the sheets and hide the intro sheet

I tried using a sub I had at hand: ShowSheets
but it failed at this line: s.Visible = True
(1004: Method 'Visible of Object'_Worksheet' failed)

Thanks
---
Sub ShowSheets()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.Visible = True
Next
End Sub

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi, I tripped up at this line when I tested a wrong password entry in the
set-up using Patrick's sample code (his code is pasted below)

Worksheets("sheetHidden").Range ("B1")

Msg: Object doesn't support this property or method (Error 438)

Not sure how to proceed further (I'm using Excel 97)

Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
------------------
Sample code received from Patrick
---------------------
To a new workbook, add a standard module and copy this code:

Option Explicit
Sub Auto_Open()
UserForm1.Show
End Sub

add a userform (Userform1)
place on the form a button and a textbox. add the following code to the
userform's code page:

Option Explicit

Private Sub CommandButton1_Click()
Dim index As Long
If Not CheckName(TextBox1.Text) Then
index = Worksheets("sheetHidden").Range("B1") + 1
If index 3 Then
ThisWorkbook.Saved = True
Application.Quit
Else
Worksheets("sheetHidden").Range ("B1")
MsgBox "User not recognised"
End If
Else
Unload Me
End If
End Sub
Private Function CheckName(sName As String) As Boolean
Dim rCol As Range
Set rCol = Worksheets("sheetHidden").Range("A:A")
On Error Resume Next

CheckName = Application.WorksheetFunction.Match(sName, rCol, False) < 0

On Error GoTo 0

End Function

The code expects a worksheet named "sheetHidden". In a cells in column A of
this sheet, type in usernames. Note that the cell B1 should be empty - the
code uses it to count the number of tries