Error in Code

[Karen McKenzie]

I have the two pieces of code below in my spreadsheet. When I run the
Engineering macro I get a run time error 1004 with the message "Unable to
set the Hidden Property of the range Class for the following line.

Range("Long_Lease").EntireRow.Hidden = False

This was working fine until I added the worksheet change code to autofit the
columns and still works if I remove the reprotection line of this part of code

Is there a way I can overcome this?

Private Sub Worksheet_Change(ByVal Target As Range)

Let HiddenCells = Range("AV3")
Activesheet.Unprotect Password:=HiddenCells
Me.Application.ActiveWorkbook.RefreshAll
Target.EntireColumn.AutoFit
On Error Resume Next
Target.Dependents.EntireColumn.AutoFit
Activesheet.Protect Password:=HiddenCells, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub



Sub Engineering()
Dim P1 As Worksheet

Set WS = ThisWorkbook.Activesheet
Dim C As Long
For C = 65535 To 65 Step -1
Let HiddenCells = Range("AV3")
Activesheet.Unprotect Password:=HiddenCells
If WS.Cells(C, 1).Value = "E" Then
WS.Cells(C + 1, 1).EntireRow.Insert
Range("Engineering").Select
Range("Engineering").EntireRow.Hidden = False
Selection.Copy
WS.Cells(C + 1, 1).Activate
Activesheet.Paste
Range("Engineering").Select
Range("Engineering").EntireRow.Hidden = True
WS.Cells(C + 1, 1).Activate
Activesheet.Protect Password:=HiddenCells, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Exit Sub
End If
Next C
End Sub

 

[Dave Peterson]

You can stop the worksheet_change event from firing by including
application.enableevents = false
right before you start making changes to the worksheet.
Then make your changes
and turn on events once more with:
application.enableevents = true

Sub Engineering()
Dim P1 As Worksheet

Set WS = ThisWorkbook.Activesheet
Dim C As Long
For C = 65535 To 65 Step -1
Let HiddenCells = Range("AV3")
Activesheet.Unprotect Password:=HiddenCells
If WS.Cells(C, 1).Value = "E" Then

application.enableevents = false '<-- added

WS.Cells(C + 1, 1).EntireRow.Insert
Range("Engineering").Select
Range("Engineering").EntireRow.Hidden = False
Selection.Copy
WS.Cells(C + 1, 1).Activate
Activesheet.Paste
Range("Engineering").Select
Range("Engineering").EntireRow.Hidden = True
WS.Cells(C + 1, 1).Activate
Activesheet.Protect Password:=HiddenCells, DrawingObjects:=True,
Contents:=True, Scenarios:=True

application.enableevents = true '<-- added

Exit Sub
End If
Next C
End Sub

 

[Karen McKenzie]

Thanks Dave

That won't work for me as the spreadsheet is being designed for use by a
large number of users and the macro is used to allow them to add as many rows
as they require each time they update the sheet with data.

 

[Joel]

When you protect a sheet the name ranges are no longer valid. You can prove
this by going to Insert Menu and selecting Names. All the names options are
highlighted. Now if you protect the worksheet under tools menu protect the
worksheet. then go back and look at names, they are disabled. Unprotecting
the worksheet will enable the Names menu.

the 1004 error you are getting is because the Name "ENGINEERING" isn't
recognized when the worksheet is protected.

 

[Dave Peterson]

I'm not sure why you'd want the event macro to run each time a new row is
inserted, but that's your choice.

Maybe you could just unprotect the sheet after each step that causes the event
macro to run (which protects the sheet).