Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Auto Open macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •