Lock non-blank used cell using VBA

**Anu shisha** · 05-12-2009, 06:10 AM

Hi,

This is Anu & i am new to this forum. Before i proceed to the query, let me be clear that i am an illiterate when it comes to programming / vba. But i am mad about excel and love to explore things out of it.

I am currently working on the attached sheet and all I want to happen is, once data is entered and when the user tries to save the file, the cells with data should get locked. I have done this using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim WS As Worksheet

Set WS = Sheets("Sheet1")
With WS
.Unprotect Password:="mkmumbai"
.UsedRange.Locked = True
.UsedRange.SpecialCells(xlCellTypeBlanks).Locked = False
.Protect Password:="mkmumbai"

End With
End Sub
_______________________________
However i have merged cells in column C & D (C5 to D65536) and the above code is not working on these. I know for techies this query can be very silly and will be resolved very easily.

PS: I cannot change any format in this sheet as this is followed by our company at global level in the specific pattern only.

I need this to get resolved on a very urgent basis, so ur quick response would be MUCH APPRECIATED

**JBeaucaire** · 05-12-2009, 12:53 PM

Click on cell B1 and click the MERGE icon to UNMERGE the cells at the top of your sheet.
Select the range of cells B1:N1
Press Ctrl-1 to open the Format Cells box
Click on Alignment > Horizontal > Center Across Selection
Click OK
Paint the cells green.

Now you have the APPEARANCE of a merged range without the pesky macro-killing merged cells actually being there.

Your macros will behave much better now.

**Anu shisha** · 05-14-2009, 12:10 AM

Thanks for that info, it helped me a bit but not in full as i have lot of other cells merged in B, C & D columns too beginning from cell B5. I know macros might not work fine with merged cells, but if any one can help me overcome this issue, it would be great

**JBeaucaire** · 05-14-2009, 01:54 AM

You overcome macros not interacting with merged cells by unmerging them. That's why the macros even tell you "cannot change merged cells". Pretty straightforward.

**Anu shisha** · 05-14-2009, 02:02 AM

Oh thanks, i have now unmerged all the cells and the macros are working fine.

**jonoakdale** · 09-13-2010, 11:20 PM

I realize this post is old, but maybe this response will be helpful to someone else with the same question.

You can actually lock/unlock merged cells.

First check if the cell is merged, such as:
If cell.MergeCells = True

If it is, set a range object to the merged range, such as:
Set mergedRange = cell.MergeArea

Then simply set the locked status of the merged range object, such as:
mergedRange.Locked = True

An entire solution may look something like this:
If cell.MergeCells = False Then
cell.Locked = True
cell.Interior.Color = RGB(227, 227, 227)
Else
Set mergedRange = cell.MergeArea
mergedRange.Locked = True
mergedRange.Interior.Color = RGB(227, 227, 227)
End If