Merge Cells and Locked (2000 and 2003)

Topic

New Reply

I have a worksheet with a range made up of merged and unmerged cells. What is need is a way Select a range of both unmerged cells and merged cells and unlock cellls within the Selection.
The attached workbook show the problem. If you select any range other than the cell “Target Cell” the Macro runs. If you select the “Target Cell” or a range containg that cell it fails.
Can anyon provide assistance.

Tom Duthie

Reply | Quote

Replies

Your problem is an excellent illustration of why you should avoid merged cells. Instead of merging them, you can set their horizontal alignment to “Center across selection”. The visual effect is the same, but your macro will work.

Reply | Quote

Further to Hans’ suggestion – write (or record) a macro in your Personal.xls:

Sub CenterAcrossColumns
With Selection
   .HorizontalAlignment = xlCenterAcrossSelection
   .MergeCells = False
End With
End Sub

and then either delete the existing “Merge and Centre” button and assign this macro to a new button, or just assign the macro to the exisiting “Merge & Centre” button. It will save this kind of aggravation in the future, since you will not have to deal with merged cells.

Reply | Quote

Thanks for the excellent suggestions and timely response
Tom Duthie

Reply | Quote

Thanks Hans
I never noticed that feature before. Now! if there were only an equivalent capability across rows.

Reply | Quote

In addition to Hans’ suggestion, you could change your line to:

MyCell.MergeArea.Locked = False

which will expand each “cell” to the entire merged area before working on it (which is required due to its merging)

Steve

Reply | Quote