Does Excel VBA allow you to lock only the content of cells, not the cells themselves?

Question

I need to lock (make it read-only) some particular cells in my VBA macro. I do it the following way:

Range(Cells(begin_row, begin_column), Cells(final_row, final_column)).Select
If Selection.Locked = True Then
    Selection.Locked = False
End If
ActiveSheet.Protect Contents:=True

It works, however I have two problems:

1. It appears that, while rows in the range (begin_row, final_row) are locked correctly, columns in that row range are locked not only in the range (begin_column, final_column) but anywhere else. Which is strange and undesired as an user may want to put some data or comment to the right from the frozen columns.

2. The columns titles could be quite long and an user may want to expand the column, however he cannot do it, as it is locked. So, I wonder: is it possible to lock only the content of the cells, yet make the cells themselves expandable? Or, if this is impossible, how to set the cell width in VBA or C# (the data is outputted from C# code actually)?

Thank you

Answer 1

Cells are by default locked - which can be confusing. First unlock all cells, Select only cells you want to be locked Then lock the cells and protect the sheet

Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1:D16").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Answer 2

Of course you can! They just hide everything so well it's impossible to find!

You want to look at the "AllowFormatting*" properties that go along with the "Contents" property. You can find the full list in the object browser under Excel::Protection. There's one for every radio button you'd check if doing this manually.

The property is updated in a comma separated list right where you set the Contents. So you could use:

ActiveSheet.Protect AllowFormattingColumns:=True, _
AllowFormattingRows:=True, Contents:=True

To check the current state you use the "Protection" class, and property of the same name:

ActiveSheet.Protection.AllowFormattingColumns

As an aside, in case your code isn't just paraphrased: It's a good idea when calling on parts of a workbook to specify your workbook/sheet too. It's not necessary, but for a few extra keystrokes can save you a multitude of headaches. I try to be as specific as I can be. If the sheet number will never change than I specify by sheet index. If the workbook name will never change, I specify that too. It keeps me from running macros on the wrong sheet when I have 20 of them open, and hit F5! You can go a little further and let MS back you up with Option Explicit too, but that's a conversation for another thread.

You could declare/set your current worksheet as an Object at the top, and then use it throughout like:

Dim ws As WorkSheet
set ws = ActiveSheet

and your snippet becomes

Dim ws As WorkSheet
set ws = ActiveSheet
'Or better yet set ws = ActiveWorkbook.Sheets(1)'<-- Change your sheet here

ws.Range(Cells(begin_row, begin_column), Cells(final_row, final_column)).Select
If Selection.Locked = True Then
    Selection.Locked = False
End If

ws.Protect AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, Contents:=True

Good luck!