Making sheets visible and hidden to users

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>