Volatile Locked Cells

Carp

New Member
Joined
Dec 4, 2003
Messages
48
I’ve set all fonts to be black. Then I made conditional
formatting on all cells with this:

Code:
=CELL(“protect”;A1)=0

In order to make all cells that are not locked have blue fonts.

Then I put this code in sheet1:

Code:
Public Sub LockUsedRange()
    
        
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    
    ActiveSheet.UsedRange.Locked = True
    Range("A1").Select ‘just for now to “unselect” all the cells. 
    
    
End Sub

It seemed to work fine at first but I soon found out that it’s very
“unstable”. It’s like some cells get locked at random… or not
locked. I don’t quite know how to put it but it’s very unpredictable.
Maybe it’s some Range “stuff”?

I'm using Excel 2003 and Windows XP
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Carp

New Member
Joined
Dec 4, 2003
Messages
48
I guess this is a stupid question somehow. lol :LOL: I'll leave it for a day or
2 more and then delete it if it's still dead.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi I think its because changing the locked value to true/false doesnt cause a recalculation.

1. In a new sheet Enter this formula into B1 =CELL("protect",A1)
2. Change cell A1 from Locked to Unlocked (Format|Cells|Protection tab)

You see nothing happens. The formula in B1 remains the same because the act of changing the locked cells status does not cause a recalculation.

Now enter a value in A1 and this should cause a recalculation and now cell B1 will have 0. Because of this your formatting will not be applied until the next recalculation.

You could try Application.Calculate to force it in your code after you have changed these values.
 

Carp

New Member
Joined
Dec 4, 2003
Messages
48
Hi parry, thanks very much for your reply. :biggrin:

I see what you mean because I tried your example out and it works just like
you say. But it’s still the same when I recalculate on my original sheet, so I
tried to “circle in” the locked (black fonts) cells and it seems just like they
form a square and lock all cells within it.

My brain is on overload. lol This is an interesting problem I think.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Dunno try CalculateFull then. This code worked for me to change the value of the B1 formula as per my previous example. It just toggles the lock on or off each time you run it.

Code:
Sub ToggleLock()

If [a1].Locked = True Then
[a1].Locked = False
Else
[a1].Locked = True
End If

Application.CalculateFull

End Sub

You know the CF is only just testing for formatting not making any changes aye?
 

Carp

New Member
Joined
Dec 4, 2003
Messages
48
Yes! I did it. :LOL:

I stumbled into a post here by accident and found this:


Code:
Dim WS As Worksheet
    
For Each WS In ActiveWorkbook.Worksheets
    With WS
        .Unprotect
        .UsedRange.Locked = True
        .UsedRange.SpecialCells(xlCellTypeBlanks).Locked = False
        '.Protect
    End With
Next WS

This line seems to be what was missing:

Code:
.UsedRange.SpecialCells(xlCellTypeBlanks).Locked = False

I was about to give up before your reply parry. Thank you very much man! (y)

Btw, yes, I know that CF does not change anything. :biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,446
Members
425,546
Latest member
DisMissive

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top