Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have put together a data collection form which is spread across five worksheets. Is there a way to make these sheets invisible so users are forced to navigate using sheet buttons, while also making only certain cells editable?

If I want to include a function to unlock everything just by pressing, for example, Ctrl + D, would I need to write a macro or can this be done another way?

share|improve this question

migrated from stackoverflow.com Oct 3 '12 at 7:40

This question came from our site for professional and enthusiast programmers.

    
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. –  Scott Holtzman Oct 2 '12 at 13:25
    
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? –  Magda Oct 2 '12 at 13:34
    
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. –  Scott Holtzman Oct 2 '12 at 13:59
2  
@ScottHoltzman - It's possible to hide the sheet tabs. In File/Options/Advanced/Display options for this Workbook/Uncheck Show Sheet Tabs –  ooo Oct 2 '12 at 14:02
    
@Magda: yes to all your questions.. gimme 15 min... let me post an answer in detail. –  Siddharth Rout Oct 2 '12 at 14:14

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
+1 For overall awesomeness in explanation! –  Scott Holtzman Oct 2 '12 at 14:46
    
This is really great thanks! :D –  Magda Oct 2 '12 at 15:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.