Hey there,

I'm a HUGE novice with excel programming, through self-teaching I've covered
quite a few simple commands but still there are loads to learn - yay.

Anyhoo, I want to be able to control what sheets some of my staff @ work
have access to.
I can easily make the tabs hidden, but unfortunately most of my staff aren't
that simple!

What I have so far is a simple userform system which checks payroll numbers
and passwords - and their access privilege...
What I want to be able to do is say... give level 1 users access to a single
"staff" sheet...
Give level 2 users access to "staff" and "holiday" sheets...
give level 3 users access to "staff", "holiday" and "reports" sheets...

I am aware of the Visible, Hidden and VeryHidden variables for sheets - how
to use them is the issue...

I keep coming up with ideas like...

If user_payroll.Value = Reference("PAYROLL1") Then
password_form.Show
Else
MsgBox("Payroll not recognised."), , "Oops!"
----------------------------------------------------------------------------------------------
If user_pass.Value = Reference("PASSWORD1") Then
Make Sheets("staff").Visible
Else
MsgBox("Password not recognised."), , "Oops!"


Then on logging out/closing the spreadsheet, the sheet(s) would become
VeryHidden again...

Any ideas how I can make this work - like I said I'm self taught and I can't
find any good learning resources for this - the books I have on excel VBA
don't mention anything like this.

All input _WILL_ be greatly appreciated!

Also - any ideas on how I could instead create a similar system by using an
excel spreadsheet as a database then taking all the login details from there
would be excellent.

thanks,

Kai

assumes user_payroll and user_password are textbox names in the respective
userforms.

in the OK button click event of the userform that queries for payroll number

If user_payroll.Value < Range("PAYROLL1").Value Then
MsgBox "Payroll not recognised.", , "Oops!"
exit sub
Else
unload me
password_form.Show
end if
----------------------------------------------------------------------------
------------------

in the OK button click event of the userform that queries for password
(pasword_form)
If user_pass.Value < Range("PASSWORD1") Then
MsgBox("Password not recognised."), , "Oops!"
else
unload me
thisworkbook.Unprotect Password:= "ABCD"
Sheets("staff").Visible
ThisWorkbook.Protect Password:= "ABCD"
end if

In the Beforeclose event,

thisworkbook.Unprotect Password:="ABCD"
for each sh in thiworkbook.worksheets
if sh.Name < "Welcome" then
sh.Visible = xlSheetVeryHidden
end if
Next
Thisworkbook.Protect Password:="ABCD"
application.EnableEvents = False
thisworkbook.Save
Application.enableEvents = True

would be basic. Not sure how you intend to implement you levels. I assume
it would be progressive and you would get a level, then look a for a
password for that level.


--
Regards,
Tom Ogilvy

"Xlyr" wrote in message
.uk...
Hey there,

I'm a HUGE novice with excel programming, through self-teaching I've
covered
quite a few simple commands but still there are loads to learn - yay.

Anyhoo, I want to be able to control what sheets some of my staff @ work
have access to.
I can easily make the tabs hidden, but unfortunately most of my staff
aren't
that simple!

What I have so far is a simple userform system which checks payroll
numbers
and passwords - and their access privilege...
What I want to be able to do is say... give level 1 users access to a
single
"staff" sheet...
Give level 2 users access to "staff" and "holiday" sheets...
give level 3 users access to "staff", "holiday" and "reports" sheets...

I am aware of the Visible, Hidden and VeryHidden variables for sheets -
how
to use them is the issue...

I keep coming up with ideas like...

If user_payroll.Value = Reference("PAYROLL1") Then
password_form.Show
Else
MsgBox("Payroll not recognised."), , "Oops!"
--------------------------------------------------------------------------
--------------------
If user_pass.Value = Reference("PASSWORD1") Then
Make Sheets("staff").Visible
Else
MsgBox("Password not recognised."), , "Oops!"


Then on logging out/closing the spreadsheet, the sheet(s) would become
VeryHidden again...

Any ideas how I can make this work - like I said I'm self taught and I
can't
find any good learning resources for this - the books I have on excel VBA
don't mention anything like this.

All input _WILL_ be greatly appreciated!

Also - any ideas on how I could instead create a similar system by using
an
excel spreadsheet as a database then taking all the login details from
there
would be excellent.

thanks,

Kai