Unable to Set Row Height after Running New Macro

[DavCode]

The following code was working fine:

Private Sub Show_Hide_Toggle_Top_Info_Rows()
Application.ScreenUpdating = False

On Error Resume Next

If Rows("5:5").RowHeight > 0.25 Then
Rows("5:18").RowHeight = 0.25
Else
Rows("5:18").RowHeight = 18
End If

Range("a1").Select
Application.ScreenUpdating = True

End Sub

I introduced a new procedure that also works fine, but subsequently prevents the first macro from working (after the new macro is successfully run). The error message is 'Unable to Set RowHeight property of range class'.

The new macro (causing the problem) is:

Private Sub Reserve()

Dim myRange As Range
Dim myCell As Range

Sheets("E").Unprotect ""
Set myRange = Sheets("E").Range("$d$23:$d$522")

For Each myCell In myRange
If IsEmpty(myCell) Then
Else
myCell.Offset(0, 27).Value = "y"
Sheets("E").Range("$af$21").Copy
myCell.Offset(0, 28).PasteSpecial Paste:=xlPasteValues
myCell.Locked = True
myCell.Interior.ColorIndex = 24
End If
Next myCell
Sheets("E").Protect ""

End Sub

I am guessing it might have something to do with locking the cells but the locked range is below the rows that have height changes applied.

Thanks..

 

[MARK858]

Have you unlocked all the cells on the sheet before running your new code? as all cells are set as locked by default, so when you protect the sheet those cells are locked as well as the one's you are specifically locking in the code.

The other (more normal way) is to unprotect the sheet at the start of the Show_Hide_Toggle_Top_Info_Rows macro and reset the protection at the end of the macro

3rd way is to set the UserInterfaceOnly to True when protecting the sheet

 

[DavCode]

Hi MARK858,

You're response triggered my memory - I forgot to add AllowFormattingRows:=True to the password protect line.
I prefer not to 'unprotect' the sheet when toggling the top row 'hide (row height = 0.25)/unhide (row height=18)' macro-assigned 'button', if it's not necessary.
I have used UserInterfaceOnly with Enable.Outlining to enable the expand/collapse feature in Excel.

Thanks!

 

[MARK858]

I take it that you are sorted now?

 

[DavCode]

Yep, thanks!