Ok. This is a mystery at least to me. I have some code that does three things:
It hides certain rows depending on choices made in one specific cell
It auto expands rows to accommodate text even in merged cells and
It sends a warning when a calculated cell exceeds 100%
All of these functions work fine but afterwards certain cells lock themselves rather than remaining unprotected as they should be. The "locked" box is filled in rather than checked. See code below. Any help is much appreciated. Have spent hours trying to fix this. Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Double
Dim cWdth As Double
Dim MrgeWdth As Double
Dim c As Range
Dim cc As Range
Dim ma As Range
Me.Unprotect Password:="PASSWORD"
If Range("B6").Value = "Individual Contributor - No direct reports" Then
Rows("98:106").EntireRow.Hidden = True
ElseIf Range("B6").Value = "Management - Has direct reports" Then
Rows("98:107").EntireRow.Hidden = False
End If
If Range("D64") > 100 Then
MsgBox "Total exceeds 100%"
End If
With Target
If .MergeCells And .WrapText Then
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
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
Application.ScreenUpdating = True
End If
End With
Me.Protect Password:="PASSWORD"
End Sub
It hides certain rows depending on choices made in one specific cell
It auto expands rows to accommodate text even in merged cells and
It sends a warning when a calculated cell exceeds 100%
All of these functions work fine but afterwards certain cells lock themselves rather than remaining unprotected as they should be. The "locked" box is filled in rather than checked. See code below. Any help is much appreciated. Have spent hours trying to fix this. Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Double
Dim cWdth As Double
Dim MrgeWdth As Double
Dim c As Range
Dim cc As Range
Dim ma As Range
Me.Unprotect Password:="PASSWORD"
If Range("B6").Value = "Individual Contributor - No direct reports" Then
Rows("98:106").EntireRow.Hidden = True
ElseIf Range("B6").Value = "Management - Has direct reports" Then
Rows("98:107").EntireRow.Hidden = False
End If
If Range("D64") > 100 Then
MsgBox "Total exceeds 100%"
End If
With Target
If .MergeCells And .WrapText Then
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
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
Application.ScreenUpdating = True
End If
End With
Me.Protect Password:="PASSWORD"
End Sub