PDA

View Full Version : Solved: Sheet Tabs



phendrena
11-27-2008, 07:47 AM
Hi there,

Is there a way to restrict the viewing of sheets tabs in VBA?
I don't specifically want to hide them.

I currently have the workbook setup with the sheet tabs not shown via the Options menu (Tools > Options > View Tab). Can I use VBA to lock-out changing that option?

Thanks,

Bob Phillips
11-27-2008, 07:59 AM
If you set the visible property to xlSheetVeryHidden they won't appear there regardless.

GTO
11-27-2008, 08:01 AM
Hi Phendrena,

You can disable the command on the command bar, but unless the user is only allowed to use/see one sheet, how do they change sheets? If the user IS limited to accessing one sheet, why not just change the other sheet(s) to xlVeryHiidden?

Mark

phendrena
11-27-2008, 08:01 AM
Hi xld,

Thanks for the reply.... I was hoping to avoid using 'Very Hiden', is this the only way?

phendrena
11-27-2008, 08:04 AM
You can disable the command on the command bar


Hi GTO,

Can't say i've ever done anything like that before.
How would I do this in VBA?

Thanks,

Bob Phillips
11-27-2008, 09:15 AM
Hi xld,

Thanks for the reply.... I was hoping to avoid using 'Very Hiden', is this the only way?

Not the only way, just the safest way.

GTO
11-27-2008, 09:29 AM
Eeks. I probably shouldn't have mentioned it, as IMO messing with the app's command bars is a decent amount of work/somewhat a hassle. Nice learning though. Check thru the KB, as I'm certain I've read neat examples, and if/when doing this and for what it's worth, I always make sure that activating another workbook reactivates factory commands and shortcuts and/or replaces any custom menu/toolbar with the app's and user's priorly (probably not a real word) selected.

For real, what would be wrong w/xlVeryHidden? The user can't bring the sheet back into view; Viola(!) problem solved! Plus, if there's a couple of sheets that you do want the user to access, keeping the tabs visible would seem to get you less, "Hey, where's the other sheet!?" calls.

Have a great day, Markus must crasheth,,,

PS - Bob way fast, Mark way slooowww.... honest, started typing initial response when none existed. Please listen to XLD, he's right.

Have a great day fellas, I'm out.

phendrena
11-28-2008, 02:29 AM
Xld, GTO...

Thanks for the replies guys.
I'm going to go with xlSheetVeryHidden.

I'll have a look through the KB for some examples of messing with the command bars - it'll be way above my level of VB knowledge but a good learning exercise for me. (I'm currently waiting for a couple of books to arrive Learn Excel from Mr Excel and VBA for Dummies - christmas presents). Still, reading through this forum, the KB and articles has helped a great deal and everyone here is great - even if i post some of the most innane stuff!

Thanks everyone.

phendrena
11-28-2008, 02:31 AM
Oh, one more innane question, is there an equivalent VeryHidden for rows and columns....?

Bob Phillips
11-28-2008, 02:46 AM
No there is just hidden or not. And of course if you have column headings it is obvious it is hidden.