Hiding worksheets & protecting cells in Excel 2010

Answers

Yes it is possible to hide the worksheets and at the same time ensure that they are not displayed in the sheet tabs

Let's take an example

Create a sheet called DASHBOARD. This is the sheet from where you will be calling the rest of the sheets. Let's say there are 3 more sheets Magda1, Magda2 and Magda3

On your DASHBOARD sheet create 3 buttons. Let's call them Open Magda1, Open Magda2 and Open Magda3 See Screenshot.

enter image description here

Next Double click Open Magda1 and paste this code

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet

    Sheets("Magda1").Visible = True

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Magda1" Then ws.Visible = xlSheetHidden
    Next
End Sub

Similarly, you will have codes for the other two buttons

Private Sub CommandButton2_Click()
    Dim ws As Worksheet

    Sheets("Magda2").Visible = True

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Magda2" Then ws.Visible = xlSheetHidden
    Next
End Sub

Private Sub CommandButton3_Click()
    Dim ws As Worksheet

    Sheets("Magda3").Visible = True

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Magda3" Then ws.Visible = xlSheetHidden
    Next
End Sub

Next go to Magda1 sheet and put a button on the top left (or as per your choice). Call it BACK This button will take us back to the DASHBOARD. See screenshot

enter image description here

Paste this code in the click event of the button

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet

    Sheets("DASHBOARD").Visible = True

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "DASHBOARD" Then ws.Visible = xlSheetHidden
    Next
End Sub

Similarly place BACK button on the other two sheets and add the above code.

One Last thing. Click on File tab and then click on Options. Next click on Advanced tab in the Excel Option dialog box and uncheck the checkbox which says Show Sheet Tabs

enter image description here

Finally come out of Design Mode and you are done :)

enter image description here

Siddharth Rout

Posted 2012-10-02T12:12:33.023

Reputation: 412

If i want to include a function to unlock everything just by pressing Ctrl+d ... would I need ... a macro - Yes! There is no other way. making only certain cells editable - see sheet protection. make these sheets invisible so users are forced to navigate using sheet button - this is confusing. You can make sheets invisible and set up buttons to open them, but once opened you have to find a way to hide them again when the user leaves the sheet. Or if, you can explain more of what you are after, you may get more help. – None – 2012-10-02T13:25:17.873

I've managed to protect sheets and set the editable ranges, its just that macro that i need now. What i meant earlier is that the sheets i've created have some buttons (next, previous, menu etc), i would like the user to navigate through the workbook using these rather than seeing the sheets as tabs on the bottom of the screen.. Does that make sense? – None – 2012-10-02T13:34:09.820

I don't think it's possible to block the seeing of the sheets on the bottom of the screen. At least not off the top of my head. If you want to provide an easier method of going from sheet-to-sheet, consider hyperlinking in the cells. Or, if you are using a userform, as I think you are, make it modal, then the user cannot touch anything other than the userform, even though they can see your sheets. Then you can place buttons where you can navigate to the active sheets. – None – 2012-10-02T13:59:50.633

2@ScottHoltzman - It's possible to hide the sheet tabs. In File/Options/Advanced/Display options for this Workbook/Uncheck Show Sheet Tabs – user3357963 – 2012-10-02T14:02:23.050

@Magda: yes to all your questions.. gimme 15 min... let me post an answer in detail. – Siddharth Rout – 2012-10-02T14:14:59.887