INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel - VBA... locking out users from accessing specific tabs.

Excel - VBA... locking out users from accessing specific tabs.

(OP)

I was wondering if there is an easy way to lock out users from accessing tabs. I found code that prevents the user from right-clicking the active tab and choosing "Unhide"... (see below)

CODE

Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("S&heet").Controls("&Unhide...").Enabled = False

But of course it interfere's with VBA opening and closing hidden forms. So I've had to add True/False code to every Procedure that opens a form.... well somehow/someway, sometimes... things screw up and then I have issues opening and closing forms. It's almost like Excel is too slow and or skips code. I don't get it. Is there another way of doing this?

Also as far as Protecting pages. I have most of my datasheets protected so users can't affect them, only VBA can thru forms. I use this code:

CODE

Dim AdminPass As String
AdminPass = Sheets("Office Form").Range("U2").Value
ThisWorkbook.Sheets("Office View").Unprotect Password:=AdminPass

Then of course, protect it again once the code has processed. Is there a better way of doing it, or is this it?

RE: Excel - VBA... locking out users from accessing specific tabs.

"Form" means worksheet? Instead of annoying users with modyfying interface (they may need it for other worksheets) either in VBE or by code set sheet's visibility to xlVeryHidden. Another option is to protect workbook's structure.
You can process protected worksheet with vba, this requires repeating protecting the sheet when you open workbook (this setting is not saved):
ThisWorkbook.Sheets("Office View").Protect Password:=AdminPass, UserInterfaceOnly:=True

combo

RE: Excel - VBA... locking out users from accessing specific tabs.

(OP)
"Office Form" is the name of the tab... like Sheet1.... thanks on the note for protecting the worksheet... I'll try it and get back with you. I'm not spending as much time on the project as I'd like... every other day at best.... thanks for the reply though.

Jeff

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close