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).

Karen McKenzie wrote:

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.

"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson