Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

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

  1. #11
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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......
    Kind Regards,
    Ivan F Moala From the City of Sails

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

    Default

    basically what i have decided to have is an Auto_Open 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!)

  3. #13
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 18:45, Daniel Cremin wrote:
    basically what i have decided to have is an Auto_Open 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.


    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards, Duane
    Office2010 in Win7

  5. #15
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards, Duane
    Office2010 in Win7

  7. #17
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

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
  •