Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Hide properties in one tab

    Loungers - is there away to hide thinks like Scroll bars and forumla bar in one tab without affecting other tabs in the speadsheet?

    Any assistance/thoughts etc would be appreciated.

    Thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Only using code. Scrollbars are a workbook-level property and the Formula bar is an application-level one. If the sheet is protected, the formulas in cells with the Hidden property set are not visible in the formula bar, if that helps?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Verada

    As Rory says, you can do it with vba code.

    If it is only one sheet where you want to hide things, you could
    right-click on that particular sheet
    select View Code
    ..and then copy this code to that sheet:

    Code:
    Private Sub Worksheet_Activate()
    
    Application.DisplayFormulaBar = False       'hide formula bar
    With ActiveWindow
        .DisplayHeadings = False                'hide column/row headers
        .DisplayHorizontalScrollBar = False     'hide horizontal scrollbar
        .DisplayVerticalScrollBar = False       'hide vertical scrollbar
    End With
    
    End Sub
    
    
    Private Sub Worksheet_Deactivate()
    
    Application.DisplayFormulaBar = True       'show formula bar
    With ActiveWindow
        .DisplayHeadings = True                'show column/row headers
        .DisplayHorizontalScrollBar = True     'show horizontal scrollbar
        .DisplayVerticalScrollBar = True       'show vertical scrollbar
    End With
    
    End Sub
    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-09-12)

  5. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi All,

    Could you let me what additional code is requred to display the sheet in full screen.

    I tried
    DisplayFullScreen = True
    and
    DisplayFullScreen = false
    But had errors coming up - any thoughts

    Also after some code to hide and unhide particular sheets using a password.
    Any suggestions for this?

    Thanks for ant help

    Regards

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's:
    Code:
    Application.DisplayFullScreen = True
    For hiding/unhiding the sheets it depends on what exactly you want. Do you want the same password for all sheets? Do you want to hide/unhide multiple sheets at once, or individually?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following User Says Thank You to rory For This Useful Post:

    verada (2013-09-16)

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    PS You could use one routine for showing/hiding things since they all take the same boolean setting:
    Code:
    Sub ShowHeaders(Optional blnShow As Boolean = True)
    
       Application.DisplayFormulaBar = blnShow
       With ActiveWindow
          .DisplayHeadings = blnShow
          .DisplayHorizontalScrollBar = blnShow
          .DisplayVerticalScrollBar = blnShow
       End With
    
    End Sub
    then called using:
    Code:
    Private Sub Worksheet_Activate()
       ShowHeaders False
    End Sub
    
    Private Sub Worksheet_Deactivate()
       ShowHeaders ' the True is optional
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    verada (2013-09-16)

  10. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory,

    Will give the codes a shot in the morning

    In relation to the sheets, I have 4 sheets in the spread sheet but would like to be able to 2, say sheet 3 & 4 with same password and then hide them again when closing the spreadsheet.

    Hope that make sense - thanks for your help

    Regards

  11. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like this:
    Code:
    Sub UnHideSheets()
       If LCase$(InputBox("Enter password")) = "some password" Then
          Sheets("Sheet3").Visible = xlSheetVisible
          Sheets("Sheet4").Visible = xlSheetVisible
       End If
    End Sub
    Sub HideSheets()
       Sheets("Sheet3").Visible = xlSheetVeryHidden
       Sheets("Sheet4").Visible = xlSheetVeryHidden
    End Sub
    Note: if you want the password to be case sensitive, remove the LCase$() part.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. The Following User Says Thank You to rory For This Useful Post:

    verada (2013-09-16)

  13. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory - that looks to be working well.

    Much Appreciated

    Regards

  14. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Loungers especially Rory, thanks for your assistance.
    After some help with abit more code.
    I have 5 work sheets, looking for some code that would hide sheets 2-5 when the spreadsheet is closed.

    Also, how could the code above be modified to open the work sheet rather than show the tab?

    Any assistance would be much appreciated.

    Regards
    Last edited by verada; 2013-09-19 at 00:31.

  15. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What do you mean by "open the worksheet"? Activate it?

    For the other part I suggest you use the workbook's BeforeSave event to hide them and then show them again afterwards.
    Regards,
    Rory

    Microsoft MVP - Excel

  16. The Following User Says Thank You to rory For This Useful Post:

    verada (2013-09-19)

  17. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Verada

    Apart from naming each of the specific sheets to hide e.g
    Sheets("thisOne").visible = xlVeryHidden
    Sheets("thatOne").visible = xlVeryHidden
    Sheets("mydata").visible = xlVeryHidden
    Sheets("yourData").visible = xlVeryHidden

    ..you could also hide and show sheets according to their tab colour.
    This way, it doesn't matter what the tab sheet name is, just the colour.
    And you can use this method for any number of sheet tabs.
    (NOTE: when hiding sheets, you must always have at least one visible)

    Here are some examples..

    Code:
    '**********************************************************
    'ROUTINE TO HIDE SHEETS IF THE SHEET TAB COLOUR IS RED..
    '**********************************************************
    sub hideRedSheets()
    
    For Each zSheet In ThisWorkbook.Worksheets      'loop through all worksheets
    If zSheet.Tab.Color = vbRed Then                   'red tab
    zSheet.Visible = xlVeryHidden                   'make sheet hidden
    End If
    Next zSheet                                     'process next worksheet
    
    End Sub
    
    '**********************************************************
    'ROUTINE TO HIDE SHEETS IF THE SHEET TAB COLOUR IS GREEN..
    '**********************************************************
    sub hideGreenSheets()
    
    For Each zSheet In ThisWorkbook.Worksheets      'loop through all worksheets
    If zSheet.Tab.Color = RGB(0, 100, 0) Then       'green tab
    zSheet.Visible = xlVeryHidden                   'make sheet hidden
    End If
    Next zSheet                                     'process next worksheet
    
    End Sub
    '**********************************************************
    'ROUTINE TO HIDE SHEETS IF THE SHEET TAB COLOUR IS BLUE
    '**********************************************************
    sub hideBlueSheets()
    
    For Each zSheet In ThisWorkbook.Worksheets      'loop through all worksheets
    If zSheet.Tab.Color = RGB(0, 0, 100) Then       'blue tab
    zSheet.Visible = xlVeryHidden                   'make sheet hidden
    End If
    Next zSheet                                     'process next worksheet
    
    End Sub
    '**********************************************************
    'ROUTINE TO SHOW SHEETS IF THE SHEET TAB COLOUR IS BLUE
    '**********************************************************
    sub showBlueSheets()
    
    For Each zSheet In ThisWorkbook.Worksheets      'loop through all worksheets
    If zSheet.Tab.Color = RGB(0, 0, 100) Then       'blue tab
    zSheet.Visible = True                           'make sheet visible
    End If
    Next zSheet                                     'process next worksheet
    
    End Sub
    '**********************************************************
    zeddy
    Last edited by zeddy; 2013-09-19 at 10:59. Reason: typo

  18. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2013-09-19)

  19. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks all for your help.

    Rory - I want the worksheet to open (activate?) rather than show the tab.

    Can the code be changed to open/activate the respective sheet rather than show the tab?
    Sub UnHideSheets()
    If LCase$(InputBox("Enter password")) = "some password" Then
    Sheets("Sheet3").Visible = xlSheetVisible
    Sheets("Sheet4").Visible = xlSheetVisible
    End If
    End Sub
    Sub HideSheets()
    Sheets("Sheet3").Visible = xlSheetVeryHidden
    Sheets("Sheet4").Visible = xlSheetVeryHidden
    End Sub

    Thanks again for any assistance

  20. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You're unhiding more than one sheet - which one do you want activated? It's basically just:
    Code:
    With Sheets("Sheet3")
    .Visible = xlSheetVisible
    .Activate
    End with
    for whichever sheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  21. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Verada

    The following examples show routines which could be assigned to buttons on your startup sheet.
    The first routine is used to unhide and switch to Sheet3.
    The second example is used to Unhide and switch to Sheet4
    In the second example, whenever you switch to Sheet4, it will always display the 'top' of the sheet etc.
    You could automatically 'hide' a sheet using the Worksheet_Deactivate() routine e.g. when clicking back to the main startup sheet.

    Code:
    Sub UnHideSheet3()
    
    If LCase$(InputBox("Enter password")) = "some password" Then
    Sheets("Sheet3").Visible = xlSheetVisible
    Sheets("Sheet3").Select
    End If
    
    End Sub
    
    
    Sub UnHideSheet4()
    
    If LCase$(InputBox("Enter password")) = "my password" Then
    
    Application.Screenupdating = False		'freeze display till ready
    Sheets("Sheet4").Visible = xlSheetVisible	'unhide sheet
    Sheets("Sheet4").Select				'switch to sheet
    ActiveWindow.ScrollRow = 1			'slide screen to first row
    ActiveWindow.ScrollColumn = 1			'slide screen to first column
    End If
    
    End Sub
    zeddy

  22. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2013-09-20)

Page 1 of 2 12 LastLast

Posting Permissions

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