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 |
All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com