Make VBA code events work

[Gtasios4]

Hi all,

I want to automate my quotation tool & protect the workbook while VBAs working.

I have dependent drop-down lists on A4:A11 and so on based on the brand list selected in cell B2. Columns A, L, N, has formulas to bring values through product info sheet.

However everything works fine on uprotected mode the opposite happens while I protect the workbook. "Insert Row" & "Delete Row" macros don't work even though I've added the below codes:

Sub macroProtect3()

Sheet1.Protect Password:="abc", UserInterFaceOnly:=True

'enter code
Sheet1.Cells(1, 1) = UCase("SOLUTION QUOTATION")

End Sub

Sub Edit_Sheet1()
'Unprotect Sheet1
Worksheets("Sheet1").Unprotect

'Do Something to Sheet1

'Reprotect Sheet1
Worksheets("Sheet1").Protect
End Sub

Sub HasFormula()

For Each Rng In ActiveSheet.Range("A4:A9")

If Rng.HasFormula Then

Rng.Locked = True

Else

Rng.Locked = False

End If

Next Rng
End Sub


Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Protect UserInterFaceOnly:=True
Next ws
End Sub

Here's my quotation tool:

I use also the below event code:

Ideally I want to keep intact the row with the "Total cost" and attach a macro by inserting row above it while using the above code.

Finally here is also the macros I use in Insert & Delete:

Sub InsertROW()
ActiveCell.EntireRow.Insert
End Sub

Sub INSERTCOPY()
With ActiveCell.EntireRow
.Copy
.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
On Error Resume Next
.Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
Application.CutCopyMode = False
On Error GoTo 0
End With
End Sub

Sub DeleteEntireRow()

Selection.EntireRow.Delete

End Sub

Any help on my thread will be much appreciated.

 

[Joe4]

This seems to be the same question you asked here, is it not?

VBA: Insert row + keep formulas & Delete Entire Row on protected sheet

Hi All, I've used in my excel the below VBA code assigned for inserting a row & keep formulas, however that code doesn't work while the worksheet is protected. Any hint on how to overcome it? VBA Code: With ActiveCell.EntireRow .Copy .Offset(1).Insert Shift:=xlDown...

www.mrexcel.com

 

[Gtasios4]

This seems to be the same question you asked here, is it not?

VBA: Insert row + keep formulas & Delete Entire Row on protected sheet

Hi All, I've used in my excel the below VBA code assigned for inserting a row & keep formulas, however that code doesn't work while the worksheet is protected. Any hint on how to overcome it? VBA Code: With ActiveCell.EntireRow .Copy .Offset(1).Insert Shift:=xlDown...

www.mrexcel.com

It is more thorough.

 

[Joe4]

Please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted. All clarificiations should be posted back to the original thread.

As per rule 12, I have closed this thread so please continue in the linked thread.