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!
  • Students Click Here

*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.

Students Click Here


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

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

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


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)


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:


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


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

"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.


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!

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