Auto Open macro

[Daniel Cremin]

Hi ppl how can i get an Auto Open macro to automatically ensure that all worksheets in the workbook are locked so that only unlocked cells can be edited - also i need to be able to have all the toolbars and the row/column headers and sheet tabs automatically hidden if the file is opened on any computer

Thanks very much!

 

[Legacy 98055]

Hi
If you place this in your workbook open event all of the sheets will be protected and only unlocked cells will be available for selection let alone editing...


Dim WS
For Each WS In Worksheets
WS.Protect Password:="Password"
WS.EnableSelection = xlUnlockedCells
Next

As far as hiding stuff, this is a macro I made to get rid of all the stuff. Edit to choose what you want to make visible or not.

To show, make a copy of this macro, rename it, and change the falses to true.



Sub hide()
Dim cbar As CommandBar
Range("A1").Select
With ActiveWindow
If .DisplayGridlines = True Then .DisplayGridlines = False
If .DisplayHeadings = True Then .DisplayHeadings = False
If .DisplayOutline = True Then .DisplayOutline = False
If .DisplayZeros = True Then .DisplayZeros = False
If .DisplayHorizontalScrollBar = True Then .DisplayHorizontalScrollBar = False
If ActiveSheet.Name<> "DataLoads" Then
Debug.Print ActiveSheet.Name
If .DisplayVerticalScrollBar = True Then .DisplayVerticalScrollBar = False
End If
If .DisplayWorkbookTabs = True Then .DisplayWorkbookTabs = False
End With

With Application
If .DisplayFormulaBar = True Then .DisplayFormulaBar = False
If .DisplayStatusBar = True Then .DisplayStatusBar = False
End With

For Each cbar In CommandBars
If CommandBars(cbar.Name).Visible = True And cbar.Name<> "Worksheet Menu Bar" Then _
CommandBars(cbar.Name).Visible = False
Next
End Sub
This message was edited by TsTom on 2002-03-24 04:09

 

[Dave Hawley]

Hi Daniel

Regarding the Toolbar hiding. This needs to be done correctly unless you want to upset the users. In other words ALL their toolbars that were visible (before opening yours) must be set back to how they were. In addition to this they should also be restored/hidden as they activate/deactivate other Workbooks.

I have some code that you may find helpful here:
http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm