PDA

View Full Version : Solved: Locking Merged Cells



tools
05-07-2008, 05:47 AM
Hi all,

I have a cell which is merged with another cell , say B6 and C6
now is there a way to lock these cells..?

And if i want to retrieve the value entered in that merged cell then is there a way to do that using vba.?

Simon Lloyd
05-07-2008, 05:57 AM
Try playing around with this:

Sub Lock_Merged_Copy_Merged()
Range("B6:C6").Locked = True
Range("B6:C6").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub
remember that "Locked" cells are not locked unless the woorksheet is protected!

lucas
05-07-2008, 06:23 AM
I would avoid merged cells. Try formatting for "center across selection instead. I have had so much trouble with files with merged cells that I almost refuse to work on one.

tools
05-07-2008, 06:23 AM
Thanks Simon

Is there a way to directly copy the value inside a variable or do i need to pick that value from cell A1.?

Bob Phillips
05-07-2008, 06:25 AM
Just set the cell value to the variable, the first cell of the merged cells.

Simon Lloyd
05-07-2008, 06:29 AM
Exactly as Bob explained, i only pasted the value to A1 so you could see the code in action showing you that neither the source cell nor the destination cell were selected (you don't have to select an object in order to work with it), you can paste to whichever cell you like!

tools
05-07-2008, 06:29 AM
Thanks a lot guys

:biggrin: