Disabeling/enabeling pages protected workbook

oofoo

New Member
Joined
Mar 30, 2016
Messages
4
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

Well-known Member
Joined
Dec 18, 2012
Messages
627
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
Code:
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

New Member
Joined
Mar 30, 2016
Messages
4
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

Well-known Member
Joined
Dec 18, 2012
Messages
627
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.
Code:
 and '/Code'
Perpa
 

oofoo

New Member
Joined
Mar 30, 2016
Messages
4
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.
Code:
 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

Well-known Member
Joined
Dec 18, 2012
Messages
627
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
Code:
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

New Member
Joined
Mar 30, 2016
Messages
4
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
 

Forum statistics

Threads
1,081,981
Messages
5,362,533
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top