Insert row and copy formula in protected sheet

[MattieIJClark]

In need of some assistance with the attached spreadsheet and VBA code.

Sub test()
Dim oldrow As Long
' Unprotect sheet

ActiveSheet.Unprotect Password:="p1645102"

' insert row below
oldrow = ActiveCell.Row
Rows(oldrow + 1).Insert
' copy formula cells
With Sheets("Deadlines")
.Range("T" & oldrow).Copy .Range("T" & oldrow + 1)
.Range("V" & oldrow).Copy .Range("V" & oldrow + 1)
.Range("W" & oldrow).Copy .Range("W" & oldrow + 1)
.Range("Z" & oldrow).Copy .Range("Z" & oldrow + 1)
End With
' Protect sheet
ActiveSheet.Protect Password:="p1645102", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True

End Sub

However, my autofit VBA no long works once the above has been used:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False

For Each Value In Target.Columns
Worksheets(Sh.Name).Columns(Value.Column).AutoFit
Next Value

Application.ScreenUpdating = True

End Sub

I get a debug error, I've attached a copy of the updated spreadsheet and the password is p1645102.

Any help will be greatly appreciated.

Thanks

 

[NARAYANK991]

Hi ,

Change the autofit code as follows :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
            Application.ScreenUpdating = False

            Sh.Unprotect "p1645102"

            For Each Value In Target.Columns
                Sh.Columns(Value.Column).AutoFit
            Next Value

            Sh.Protect "p1645102"
            Application.ScreenUpdating = True
End Sub

Narayan

 

[MattieIJClark]

Many thanks Narayan.

Tried the new code and got a 400 error, is there something I'm missing. VBA isn't my strong point so any help is greatly appreciated.

Thanks

 

[NARAYANK991]

Hi ,

What should I do to reproduce the error at my end ? What actions generated this error ?

Narayan

 

[MattieIJClark]

Hi Narayan,

Many thanks for your reply.

All I did was copy and paste your VBA code over the old one on my spreadsheet, I then entered some text on row 3 and used the drop down menus to make sure the resizing works.

Then I pressed the Insert Row button and this is where I get the error.

Thanks

Matt

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[MattieIJClark]

Hi Narayan,

Please accept my apologies for not coming back to you sooner.

Thank you so much for assisting with my issue on both the VBA code and the 400 error.

Thanks