PC Review


Reply
Thread Tools Rate Thread

Autofit Merged cell Code is changing the format of my merged cells

 
 
=?Utf-8?B?SkI=?=
Guest
Posts: n/a
 
      20th Aug 2007
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merged Cells Autofit Macro Code knowshowrosegrows Microsoft Excel Programming 1 11th Nov 2009 02:04 PM
Merged Cells - Format, Column, Autofit mdalby Microsoft Excel Misc 1 21st May 2004 02:16 AM
define cell for autofit of merged cells erin Microsoft Excel Programming 3 23rd Feb 2004 05:59 PM
Code for autofit/merged cells doesn't work Janet Microsoft Excel Programming 1 29th Jan 2004 02:39 AM
Merged Cells Autofit - code amendment roy Microsoft Excel Programming 7 4th Nov 2003 12:19 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:48 AM.