Error message when hiding rows in a protected worksheet

[Godot60]

Hi,

I have form that has VBA code in it that: allows users to make a selection which determines which rows they see, calculates when a certain cell has a total of more than 100% and resizes rows depending on text entered. All three work perfectly.

The problem arises when I try to protect the workbook. Once I do that, I get the following error message when the user makes a choice about what rows they see. Then i get the following message: runtime error 1004 unable to set the hidden property of the range class.

See VBA below.

Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
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:106").EntireRow.Hidden = False
End If

If Range("D64") > 100 Then
MsgBox "Percentage exceeds 100"
End If

Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
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
End Sub

 

[shg]

You could unprotect and then reprotect the sheet in the code.

 

[Godot60]

How would I do that? I'm still an early learner when it come to this.

 

[shg]

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:="Shhh"
  
  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:106").EntireRow.Hidden = False
  End If

  If Range("D64") > 100 Then
    MsgBox "Percentage 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:="Shhh"
End Sub

It would be a kindness if you would wrap your code in code tags.

 

[Godot60]

Hi again,

It works perfectly now! Thanks so much for your assistance. Much appreciated.

 

[shg]

You're welcome.