Probelm with macro
 

Hello,

I have a macro (see below) and it works great. The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.


For Each Sheet In Worksheets
Range("A1").Select
If Sheet.ProtectContents = False Then
Sheet.Protect Password:="2568", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True
EnableSelection = xlUnlockedCells
End If
Next Sheet
Range("A1").Select
' ActiveSheet.UnProtect Password:="2000"
MsgBox "All sheets are now protected!"
End Sub

--
thank you mac

Probelm with macro
 

You can view locked cells in a protected worksheet, but not if they
are hidden. Are the cells locked AND hidden?


HTH,
JP

On Jan 14, 11:22*am, mac wrote:
Hello,

I have a macro (see below) and it works great. *The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. *Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.

For Each Sheet In Worksheets
* * * * Range("A1").Select
* * * * If Sheet.ProtectContents = False Then
* * * * * * Sheet.Protect Password:="2568", _
* * * * * * * * DrawingObjects:=True, Contents:=True, _
* * * * * * * * Scenarios:=True, AllowFormattingCells:=True, _
* * * * * * * * AllowFormattingColumns:=True, _
* * * * * * * * AllowFormattingRows:=True
* * * * * * * * EnableSelection = xlUnlockedCells
* * * * End If
* * Next Sheet
* * *Range("A1").Select
' * *ActiveSheet.UnProtect Password:="2000"
* * MsgBox "All sheets are now protected!"
End Sub

--
thank you mac

Probelm with macro
 

No they are only locked. I added the last line

EnableSelection = xlUnlockedCells

hoping that would do it, but I cannot go to any of the cells. Any ideas why
it would not work? Any help is greatly appreciated.
--
thank you mac


"JP" wrote:

You can view locked cells in a protected worksheet, but not if they
are hidden. Are the cells locked AND hidden?


HTH,
JP

On Jan 14, 11:22 am, mac wrote:
Hello,

I have a macro (see below) and it works great. The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.

For Each Sheet In Worksheets
Range("A1").Select
If Sheet.ProtectContents = False Then
Sheet.Protect Password:="2568", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True
EnableSelection = xlUnlockedCells
End If
Next Sheet
Range("A1").Select
' ActiveSheet.UnProtect Password:="2000"
MsgBox "All sheets are now protected!"
End Sub

--
thank you mac

Probelm with macro
 

I tested your code and was not able to duplicate your problem.

I created a blank workbook with 5 worksheets and put some random
numbers and some simple formulas in the first sheet. I hit Ctrl-A and
on the "Protection" tab I checked "Locked" and unchecked "Hidden."
Then I ran your macro and I was able to select and view the values and
formulas I previously entered.

The only way I was unable to view a cell was when the cells were
hidden (regardless of whether they were locked).

Is there anything you might have left out?

--JP

On Jan 14, 12:54*pm, mac wrote:
No *they are only locked. *I added the last line

EnableSelection = xlUnlockedCells

hoping that would do it, but I cannot go to any of the cells. *Any ideas why
it would not work? *Any help is greatly appreciated. *
--
thank you mac



"JP" wrote:
You can view locked cells in a protected worksheet, but not if they
are hidden. Are the cells locked AND hidden?

HTH,
JP

On Jan 14, 11:22 am, mac wrote:
Hello,

I have a macro (see below) and it works great. *The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. *Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.

For Each Sheet In Worksheets
* * * * Range("A1").Select
* * * * If Sheet.ProtectContents = False Then
* * * * * * Sheet.Protect Password:="2568", _
* * * * * * * * DrawingObjects:=True, Contents:=True, _
* * * * * * * * Scenarios:=True, AllowFormattingCells:=True, _
* * * * * * * * AllowFormattingColumns:=True, _
* * * * * * * * AllowFormattingRows:=True
* * * * * * * * EnableSelection = xlUnlockedCells
* * * * End If
* * Next Sheet
* * *Range("A1").Select
' * *ActiveSheet.UnProtect Password:="2000"
* * MsgBox "All sheets are now protected!"
End Sub

--
thank you mac- Hide quoted text -

- Show quoted text -

Probelm with macro
 

EnableSelection = xlNoRestrictions


Gord Dibben MS Excel MVP

On Mon, 14 Jan 2008 08:22:01 -0800, mac wrote:

Hello,

I have a macro (see below) and it works great. The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.


For Each Sheet In Worksheets
Range("A1").Select
If Sheet.ProtectContents = False Then
Sheet.Protect Password:="2568", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True
EnableSelection = xlUnlockedCells
End If
Next Sheet
Range("A1").Select
' ActiveSheet.UnProtect Password:="2000"
MsgBox "All sheets are now protected!"
End Sub

Probelm with macro
 

JP

As OP protected the sheet with "select locked cells" disabled he would not be
able to select a locked cell to see the formula.


Gord Dibben MS Excel MVP

On Mon, 14 Jan 2008 10:17:00 -0800 (PST), JP wrote:

I tested your code and was not able to duplicate your problem.

I created a blank workbook with 5 worksheets and put some random
numbers and some simple formulas in the first sheet. I hit Ctrl-A and
on the "Protection" tab I checked "Locked" and unchecked "Hidden."
Then I ran your macro and I was able to select and view the values and
formulas I previously entered.

The only way I was unable to view a cell was when the cells were
hidden (regardless of whether they were locked).

Is there anything you might have left out?

--JP

On Jan 14, 12:54*pm, mac wrote:
No *they are only locked. *I added the last line

EnableSelection = xlUnlockedCells

hoping that would do it, but I cannot go to any of the cells. *Any ideas why
it would not work? *Any help is greatly appreciated. *
--
thank you mac



"JP" wrote:
You can view locked cells in a protected worksheet, but not if they
are hidden. Are the cells locked AND hidden?

HTH,
JP

On Jan 14, 11:22 am, mac wrote:
Hello,

I have a macro (see below) and it works great. *The only problem I am having
is that I want to be able to see in locked cells, and the macro only lets me
go into unlocked cells. I do not want to change the locked cells, just be
able to see formula and where it comes from. *Could anyone tell me what I am
doing wrong with this macro?? Any help is greatly appreciated.

For Each Sheet In Worksheets
* * * * Range("A1").Select
* * * * If Sheet.ProtectContents = False Then
* * * * * * Sheet.Protect Password:="2568", _
* * * * * * * * DrawingObjects:=True, Contents:=True, _
* * * * * * * * Scenarios:=True, AllowFormattingCells:=True, _
* * * * * * * * AllowFormattingColumns:=True, _
* * * * * * * * AllowFormattingRows:=True
* * * * * * * * EnableSelection = xlUnlockedCells
* * * * End If
* * Next Sheet
* * *Range("A1").Select
' * *ActiveSheet.UnProtect Password:="2000"
* * MsgBox "All sheets are now protected!"
End Sub

--
thank you mac- Hide quoted text -

- Show quoted text -