Prev: Formula shows as text
Next: Tables and Custom views in Excel 2007
From: Doug on 21 Apr 2010 14:57
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).
--
Doug
From: broro183 on 21 Apr 2010 15:55

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


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=197436

http://www.thecodecage.com/forumz

From: Doug on 10 May 2010 19:43
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

--
Doug


"broro183" wrote:

>
> 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
>
>
> --
> broro183
>
> Rob Brockett. Always learning & the best way to learn is to
> experience...
> ------------------------------------------------------------------------
> broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=197436
>
> http://www.thecodecage.com/forumz
>
> .
>
 | 
Pages: 1
Prev: Formula shows as text
Next: Tables and Custom views in Excel 2007