Solved: Sheet Tabs

[phendrena]

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]

If you set the visible property to xlSheetVeryHidden they won't appear there regardless.

[GTO]

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]

Hi xld,

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

[phendrena]

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]

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]

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]

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]

Oh, one more innane question, is there an equivalent VeryHidden for rows and columns....?

[Bob Phillips]

No there is just hidden or not. And of course if you have column headings it is obvious it is hidden.