How do i unhide sheet tabs, scroll bars, row and column head

[Daniel Cremin]

Hi im in really really big trouble with a macro i always thought would be easy. I need to be able to apply the following to always happen to all sheets when i open a workbook in Excel:

-Have all Sheet tabs hidden
-No Row and Column Headers visible
-Have all sheet protected so that only unlocked cells can be selected.

When the system closes and opens each times it needs to be like this! Please Please Please help me
Thanks very very much

Daniel

 

[Ivan F Moala]

On 2002-03-31 16:10, Daniel Cremin wrote:
cheers mate for the last bit of code there! It means instead of having to go to every worksheet and then do the protection i can just set each worksheet to be like that!

The ActiveSheet.EnableSelection = xlUnlockedCells 'xlNoSelection

Will only take effect when the sheet is protected......

 

[Daniel Cremin]

basically what i have decided to have is an Autpen macro that brings up the "Main Menu" worksheet, hides all pre-built toolbars (by having recorded each being hidden lol!), sets worksheet tabs to not be displayed along with Formula bar, status bar, so that basically only the "worksheet menu bar" (file, edit,etc) is visible on screen - ive read theres a way to kill even this but its far to advanced for a novice VBA user like myself - i know little some great little 'tricks' in VBA but have never actually bothered to read any of my books the whole way through to find out about Private Subs, Public Subs, etc - all i know is how to use statements such as Do Until, For...Next, Select Case, If Then Else, etc.

Anyway back to my system...

I will then have an "access underlying structure" macro called "UnlockSystem" which can when run show the Standard, Formatting, Forms, Drawing and Control toolbox toolbars, make the Sheet tabs visible and have the two underlying worksheets powering the rest of the system set to be un-protected. Adding Row and Column Headers, etc will be done manually by the user (lol or should i say developer!)

 

[Ivan F Moala]

On 2002-03-31 18:45, Daniel Cremin wrote:
basically what i have decided to have is an Autpen macro that brings up the "Main Menu" worksheet, hides all pre-built toolbars (by having recorded each being hidden lol!), sets worksheet tabs to not be displayed along with Formula bar, status bar, so that basically only the "worksheet menu bar" (file, edit,etc) is visible on screen - ive read theres a way to kill even this but its far to advanced for a novice VBA user like myself - i know little some great little 'tricks' in VBA but have never actually bothered to read any of my books the whole way through to find out about Private Subs, Public Subs, etc - all i know is how to use statements such as Do Until, For...Next, Select Case, If Then Else, etc.

Anyway back to my system...

I will then have an "access underlying structure" macro called "UnlockSystem" which can when run show the Standard, Formatting, Forms, Drawing and Control toolbox toolbars, make the Sheet tabs visible and have the two underlying worksheets powering the rest of the system set to be un-protected. Adding Row and Column Headers, etc will be done manually by the user (lol or should i say developer!)

Daniel
Just for completeness, you may alreay have this, but it is always good practice to reset the original settings upon closing.
Have you got an Auto close routine ??
You will probably need this anyway so that
IF you or user opens up with macros disabled
then at the very lest they are looking at
a pre defined sheet/setup.

 

[Duane]

I use the following to create that "clean" environment (no sheet tabs or column/row headers)...

With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub

I don't have any code to run this automatically on startup, but you can add that as well as the protect all sheets option.

 

[Mark O'Brien]

Wow, you've been digging deep into the archives. I was surprised to see my posts in this thread. I forgot about it.

I suffer from the fact that I like to find ways to break things, so if someone tried to stop me from going to sheets by hiding the tabs, I'd eventually stumble across CTRL + (Page Up/Down) for navigating through the sheets.

 

[Duane]

Oops, I accidentally deleted it (thought it was connected to the wrong thread).

Yeah, you're right, there are ways to get around such controls. But often the users just want to get their work done, and follow the designer's directions. I had very good support from my boss last year, a VP who depended on my model for his monthly meetings with the president. It'd be of no value to him to go messing with my sheets!

 

[Andy Gee]

I have been working on something similar!

The code to hide everything is below.

Private Sub Workbook_Open()
If Application.CommandBars("Worksheet Menu Bar").Enabled = True Then
Application.CommandBars("Worksheet Menu Bar").Enabled = False
End If
With ActiveWindow
.DisplayHeadings = False
.DisplayZeros = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
End With
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Forms").Visible = False
End Sub

and to unhide (beforeclose event)


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.CommandBars("Worksheet Menu Bar").Enabled = False Then
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End If
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Forms").Visible = True
With ActiveWindow
.DisplayHeadings = True
.DisplayZeros = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
.ShowWindowsInTaskbar = True
End With
End Sub

I also added a button to show the menu to view the code etc.

Sub Show_menu()

If Application.CommandBars("Worksheet Menu Bar").Enabled = False Then
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End If

End Sub

Hope this helps!

PS visit http://www.hungrybear.co.uk/compile2.zip to see how it works
This message was edited by Andy Gee on 2002-04-11 14:51