allow merging cells in protected sheet

[Doug]

When I protect the sheet, the Merge & Center button is greyed out. I need to
allow users to merge and center cells in a protected sheet (these cells are
not locked).

 

[broro183]

hi Doug,

I would recommend steering clear of merged cells as they can cause
numerous problems. As an alternative I suggest that you use the "centre
across selection" option from the dropdown in Format Cells ([ctrl + 1])
- Text Alignment - "Horizontal".

If you are using macros in your file, you could use the
"userinterfaceonly:=true" option which is available in the
Worksheet.Protect Method of Excel's VBA. Note that this setting has to
be reapplied each time the file is opened.

hth
Rob

 

[Doug]

Thanks, Rob

With my limited VB knowledge and some searching, I put in this macro and it
unprotects the sheet, merges the selected cells, and then re-protects the
sheet with the formatting I need. Works for me.

Sub MergeCells()
'
' MergeCells Macro
' merge selected cells but preserve formatting ability for unlocked cells
'
' Keyboard Shortcut: Ctrl+m
'
'
Const myPassword = "password"

ActiveSheet.Unprotect myPassword
Selection.Cells.Merge
ActiveSheet.Protect myPassword, DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True,
AllowDeletingRows:= _
True, AllowSorting:=True

End Sub