Probelm with macro

  • Thread starter Thread starter mac
  • Start date Start date
M

mac

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
 
You can view locked cells in a protected worksheet, but not if they
are hidden. Are the cells locked AND hidden?


HTH,
JP
 
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.
 
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
 
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
 
Back
Top