PDA

View Full Version : auto fit cell height for merged cells in sheet that has protected (locked) cells



CFTBrian
10-30-2019, 01:24 PM
When i use the following code it doesn't always show every line.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vhight As Single
If Target.WrapText = True Then
With Target
.Select
.RowHeight = 2
.WrapText = True
.UnMerge
.EntireRow.AutoFit
Selection.Merge
Vhight = .Width * .Height / Selection.Width
If Vhight < 25 Then Vhight = 25
.RowHeight = Vhight
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
End With
End If
End Sub

I also have struggled with protecting some of the sheet and having the VBA code work at all.

paulked
10-30-2019, 02:07 PM
If the sheet is protected then



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vhight As Single
If Target.WrapText = True Then
Unprotect
With Target
.Select
.RowHeight = 2
.WrapText = True
.UnMerge
.EntireRow.AutoFit
Selection.Merge
Vhight = .Width * .Height / Selection.Width
If Vhight < 25 Then Vhight = 25
.RowHeight = Vhight
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
End With
End If
Protect
End Sub

CFTBrian
10-30-2019, 02:51 PM
If the sheet is protected then



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vhight As Single
If Target.WrapText = True Then
Unprotect
With Target
.Select
.RowHeight = 2
.WrapText = True
.UnMerge
.EntireRow.AutoFit
Selection.Merge
Vhight = .Width * .Height / Selection.Width
If Vhight < 25 Then Vhight = 25
.RowHeight = Vhight
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
End With
End If
Protect
End Sub

CFTBrian
10-30-2019, 02:58 PM
it doesn't seem to be making the cells the correct height as some of the words (and wrapped text) are partially cut off or completely missing

paulked
10-30-2019, 03:28 PM
Isn't this all you need?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.WrapText = True Then Target.EntireRow.AutoFit
End Sub


It works for me!