Disabeling/enabeling pages protected workbook

[oofoo]

HelloHi allNew on the forum here. Looking forward to being part of the community. I have a problem that I have been struggeling with for some time. I have a proteced workbook consisting of 1 sheet and 4 pages in that sheet. The problem is;The users using the sheet may only need page 1 and 4 so somehow i have to disable the page 2,3 at start up and wait until the user reqests them.Is this at all possible and if so how could it be done? I have looked up and down and alaround with no answer. Hope you guys are the key.

 

[Perpa]

HelloHi allNew on the forum here. Looking forward to being part of the community. I have a problem that I have been struggeling with for some time. I have a proteced workbook consisting of 1 sheet and 4 pages in that sheet. The problem is;The users using the sheet may only need page 1 and 4 so somehow i have to disable the page 2,3 at start up and wait until the user reqests them.Is this at all possible and if so how could it be done? I have looked up and down and alaround with no answer. Hope you guys are the key.

oofoo,
Welcome to the forum.
I have Excel 2007 on Windows 7. The following might vary depending on what version of Excel you are using.
Without macros:
First unprotect your worksheet.
Then you can 'unlock' certain cells using the 'Home>Cells>Format>Protection' selection... then uncheck the 'Locked' box.
So you can select the range of your first page and the range of your fourth page (holding 'Ctrl' and left click and
drag to the bottom RH corner of each page), then use the'Home>Cells>Format>Protection' and uncheck the 'locked' box then press 'OK'.
Notice that locking cells has no effect until you protect the worksheet. To uprotect Pages 2 and 3, you must first unprotect the worksheet, then select the range (page) you want to unlock, unlock using 'Home>Cells>Format>Protection', uncheck the 'Locked' box and then protect the worksheet again.

With a macro:
Identify each page using the range function for each page, ie. Page 1 might be Range("A1:I39") and Page 4 Range("AB1:AJ39").
I have created 3 cases:
Case 1 - just Pages 1 and 4 are unprotected
Case 2 - Adds Page 2 unprotected as well
Case 3 - Adds Page 3 unprotected as well
Selecting Case 1 again locks/protects pages 2 and 3

Change the page ranges to fit your page layout. You can see the page ranges by doing a print preview and then backing out of the print mode. Put the following in a standard code module.
You can run the macro using 'Alt+F8', select 'Protect_Unprotect', then 'Run'.
Perpa

Sub Protect_Unprotect()
    ActiveSheet.Unprotect
    
    UserInput = InputBox("Enter '2' to Unprotect Page 2, '3' to Unprotect Page 3, or  '1'  for just Pages 1 and 4", "Protect/Unprotect Pages")

        Select Case UserInput

            Case 1    ' Just Pages 1 and 4
                Range("A1:I39,AB1:AJ39").Locked = False
                Range("A1:I39,AB1:AJ39").FormulaHidden = False
                'Pages 2 and 3 are locked/protected
                Range("J1:Z39").Locked = True
                Range("J1:Z39").FormulaHidden = True

            Case 2   'Add Page 2
                Range("J1:R39").Locked = False
                Range("J1:R39").FormulaHidden = False

            Case 3   'Add Page 3
                Range("S1:AA39").Locked = False
                Range("S1:AA39").FormulaHidden = False

        End Select
    
    ActiveSheet.Protect

End Sub

 

[oofoo]

Thank you very much. I have only tried the macro, but that works perfectly.

Is there any way to make the pages invisible to user until they need them so that only page 1 and for is visible at start up and then 2-3 can be added in as nedded?

 

[Perpa]

Thank you very much. I have only tried the macro, but that works perfectly.

Is there any way to make the pages invisible to user until they need them so that only page 1 and for is visible at start up and then 2-3 can be added in as nedded?

oofoo,
I will be travelling to watch my grandson for the next couple of days and won't be able to look at it until then.
Perhaps someone else can assist you in the meantime. I will check back and see how it is going. If this is urgent
you can show the code I provided in a new post with the revised need criteria and thread title. Be sure to
enclose my code between bracketed 'code' designators so it will display properly... ie.

 and '/Code'
Perpa

 

[oofoo]

oofoo,
I will be travelling to watch my grandson for the next couple of days and won't be able to look at it until then.
Perhaps someone else can assist you in the meantime. I will check back and see how it is going. If this is urgent
you can show the code I provided in a new post with the revised need criteria and thread title. Be sure to
enclose my code between bracketed 'code' designators so it will display properly... ie.

 and '/Code'
Perpa[/QUOTE]


Thank you for your time Perpa I am in now hurry so if someone else helps that is okay to.

I hope you have a nice trip.

 

[Perpa]

Thank you for your time Perpa I am in now hurry so if someone else helps that is okay to.

I hope you have a nice trip.

oofoo,
I had a few minutes (my grandson is in school now) so I took another look at hiding/unhiding sheets 2 and 3.
Below is the revised code. See the last line of each Case.
Perpa

Sub Protect_Unprotect()
    ActiveSheet.Unprotect
    
    UserInput = InputBox("Enter '2' to Unprotect Page 2, '3' to Unprotect Page 3, or  '1'  for just Pages 1 and 4", "Protect/Unprotect Pages")

        Select Case UserInput

            Case 1    ' Just Pages 1 and 4
                Range("A1:I39,AB1:AJ39").Locked = False
                Range("A1:I39,AB1:AJ39").FormulaHidden = False
                'Pages 2 and 3 are locked/protected
                Range("J1:Z39").Locked = True
                Range("J1:Z39").FormulaHidden = True
                Columns("J:AA").EntireColumn.Hidden = True     'hides pages 2 and 3

            Case 2   'Add Page 2
                Range("J1:R39").Locked = False
                Range("J1:R39").FormulaHidden = False
                Columns("J:R").EntireColumn.Hidden = False          'Unhides page 2

            Case 3   'Add Page 3
                Range("S1:AA39").Locked = False
                Range("S1:AA39").FormulaHidden = False
                Columns("S:AA").EntireColumn.Hidden = False          'Unhides page 3
        End Select
    
    ActiveSheet.Protect

End Sub

 

[oofoo]

Hello Perpa

Thank you once again for your help.

I wont be able to test it before next week due to work, but i will get back to you as soon as possible, thanks again