Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JB JB is offline
external usenet poster
 
Posts: 115
Default Autofit Merged cell Code is changing the format of my merged cells

Hi,

I know merged cells are awful, but I have to use them and I am using the
code below to autofit the merged cells in my protected sheet (protection
doesn't have a password). It's working great, except after data is entered
into the merged cells they are being reformatted as particially locked which
doesn't allow the user to go back to those cells if corrections are needed.
(I can see this because if I unprotect after data is entered and look at the
cell's format, the "locked" check box is not checked but completely filled
in). The only merged cell this is not happening to is the first cell on the
sheet. Can anyone find a solution for this? Thank you!!!!!!!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' password
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' password
End If
End With
End Sub
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a way to Autofit Merged Cells in a row? JLSmith Excel Discussion (Misc queries) 2 August 1st 06 04:49 PM
how do i link merged cells to a merged cell in another worksheet. ibbm Excel Worksheet Functions 3 April 27th 06 11:40 PM
Row Autofit on Merged Cells Jluo Excel Discussion (Misc queries) 1 April 18th 05 02:37 PM
Autofit Merged Cells in Excel kchambers Excel Discussion (Misc queries) 6 March 5th 05 05:57 PM
Autofit in Merged Cells? Mick Excel Discussion (Misc queries) 4 February 14th 05 06:15 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"