Clearing unlocked cells

[Lanox]

Hello all,

I have locked an entire worksheet, except a specific range X. In the next step I run a macro that clears this range in VBA. After clearing the range, all the cells in it are suddenly locked.

Why is this happening?
And how can it be solved?

Thank you,

Lanox

[royUK]

hat code are you using?

[TMS]

Guess it depends on what your code does.

Can you post the code? Ideally, in a sample workbook that demonstrates the problem.

Regards, TMS

[Lanox]

range("X").clear

[TMS]

If you seriously want someone to look at your problem, please post all the code so it can be seen in context.

In order to test your code, it is highly likely that you will need to post a sample workbook.


Regards, TMS

[OnErrorGoto0]

Try using ClearContents instead. I suspect you are resetting the style to Normal, which by default probably has the Locked property set to True.

[Lanox]

Hi all,

thx for all the remarks.

I had an example sheet with the problem, but the settings here at work prevent me from uploading it. Sorry for that. The line of code was the only line in a subroutine and demonstrated the problem.

The problem is exactly what OnErrorGoto0 says: the Locked property is part of the cell formatting and is reseted to its defaults when the cell is cleared. The default setting makes it a locked cell.

The solution that worked for me has two parts. I first had to allow format changes by the user in the protection options, and then I copied an empty cell with the desired formats and properties to the entire range. It is not 100% safe since the user could alter this empty cell too, but you can make it hard to access it by putting a locked cell in the same column and set the column width to 0.

Thanks again to everyone who took the time to look at my problem.

[OnErrorGoto0]

Did it not work using ClearContents instead of Clear? That should not affect formatting.

[Lanox]

In my case it didn't because of some other features of the sheet. The range contains merged cells and might have to be overwritten by merged cells of a different size. ClearContents doesn't eliminate this part of the formats, so additional code would be needed. My solution was easier in this particular case.