Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Merge Cells and Locked (2000 and 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge Cells and Locked (2000 and 2003)

    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merge Cells and Locked (2000 and 2003)

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

    <pre>MyCell.MergeArea.Locked = False</pre>


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

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge Cells and Locked (2000 and 2003)

    Further to Hans' suggestion - write (or record) a macro in your Personal.xls:
    <pre>Sub CenterAcrossColumns
    With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    .MergeCells = False
    End With
    End Sub
    </pre>


    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.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Merge Cells and Locked (2000 and 2003)

    Thanks for the excellent suggestions and timely response
    Tom Duthie

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge Cells and Locked (2000 and 2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •