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

[Gtasios4]

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, CopyOrigin:=xlFormatFromLeftOrAbove
On Error Resume Next
.Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
Application.CutCopyMode = False
On Error GoTo 0
End With

I am also facing a problem with the below code assigned as well, while the worksheet is protected. Any help on how to overcome it?

Sub DeleteEntireRow()

Selection.EntireRow.Delete

End Sub

 

[Gtasios4]

Is that the code you currently have in your workbook?

If so, what part is erroring out?
Can you show us the exact row?

You may need to show us the part of the code that you cut out.

Hi Joe4,

Here's what I get when the sheet is protected

 

[Joe4]

Hi Joe4,

Here's what I get when the sheet is protected

View attachment 63735

I would fully expect that, if your sheet is protected.
As I said before, you need to unprotect the sheet first before doing that.
Do you understand this?

 

[Gtasios4]

I would fully expect that, if your sheet is protected.
As I said before, you need to unprotect the sheet first before doing that.
Do you understand this?

So in order the instert row macro works it must be unprotected? Is there any chance to overcome that? Just have a look on the below if it helps you

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

Attachments​

 

[Joe4]

So in order the instert row macro works it must be unprotected? Is there any chance to overcome that?

Yes, I have said that repeatedly! I am not sure why you seem to ignore my answers.
As I mentioned, you need to unprotect the sheet first, but you can do that right in the VBA code, and re-protect again at the end, so it is only unprotected long enough to run the code.
So it will not be unprotected for anyone to do anything manually.

I see you are protecting the sheet with a password here in this line:

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

but you are not using a password in the "Unprotect" line!

Worksheets("Sheet1").Unprotect

If your sheet is password protect, you MUST supply the password on the line of code that unprotects the sheet.

 

[Gtasios4]

Yes, I have said that repeatedly! I am not sure why you seem to ignore my answers.
As I mentioned, you need to unprotect the sheet first, but you can do that right in the VBA code, and re-protect again at the end, so it is only unprotected long enough to run the code.
So it will not be unprotected for anyone to do anything manually.

I see you are protecting the sheet with a password here in this line:


but you are not using a password in the "Unprotect" line!

If your sheet is password protect, you MUST supply the password on the line of code that unprotects the sheet.

Dear Joe4,

I don't know much VBA. Let's take it again from the start. I've cleared the above codes in kept only the below through the portal found: VBA Protect / Unprotect Worksheets - Automate Excel

In sheet 1 I have the below code (General):

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

'Do Something to Sheet1

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

In ThisWorkbook I have :

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.protect UserInterfaceOnly:=True
Next ws
End Sub

Are they wrong? Could you please help me with the code in order to make VBA works in protected workbook?

I really appreciate your valuable help!

 

[Gtasios4]

Yes, I have said that repeatedly! I am not sure why you seem to ignore my answers.
As I mentioned, you need to unprotect the sheet first, but you can do that right in the VBA code, and re-protect again at the end, so it is only unprotected long enough to run the code.
So it will not be unprotected for anyone to do anything manually.

I see you are protecting the sheet with a password here in this line:


but you are not using a password in the "Unprotect" line!

If your sheet is password protect, you MUST supply the password on the line of code that unprotects the sheet.

Joe fortunately the code works in read-only mode while being protected.

 

[Gtasios4]

Hi Joe4,

I want to attach in the "Insert Row" button a macro where a row will be added and keeping the format and formulas of the row 5 while keeping the row with the total cost intact. So somehow to have it steady and add rows above it. Is that possible?

In cell B2 as well as in B4,B5 etc I have depended drop down lists. Thus, when I change the value in cell B2 I want the below code to run in range B4 but also in the inserted rows (ranges B5:B...) if i press the "Insert Row" button.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Range("B4").Value = "Choose the model"
End If

If Target.Address = "$B$2" Then
Range("B5").Value = "Add extra equipment"
End If

And a last question, is there any VBA code to attach in a button named "start over again" something like to reset the tool in terms of values and the user starts all over again to configuring..

Best Regards

 

[Joe4]

Your original question was around the worksheet protection not allowing your code to run properly.

This appears to be a whole different question, and as such, should be posted to a new thread.

 

[Gtasios4]

You need to unprotect the sheet first.
You can do that right in your VBA code, and then re-protect it in the same code (at the end).

See here for details: VBA Protect / Unprotect Worksheets - Automate Excel

Dear Joe4,

I am coming back into that thread, having a question.. I am having the below codes in my worksheet in order to make VBA works on protected mode.

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


'Do Something to Sheet1


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

Private Sub workbook_open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.protect UserInterfaceOnly:=True
    Next ws
End Sub

The workbook is saved in a shared file path. However, it worked perfectly now it asks every user when he opens the worksheet as read-only to type a password to unprotect it..Any feedback why's that happening? I don't know maybe the code crashed?

 

[Joe4]

I have no idea.

Dear Joe4,

I am coming back into that thread, having a question.. I am having the below codes in my worksheet in order to make VBA works on protected mode.

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


'Do Something to Sheet1


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

Private Sub workbook_open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.protect UserInterfaceOnly:=True
    Next ws
End Sub

The workbook is saved in a shared file path. However, it worked perfectly now it asks every user when he opens the worksheet as read-only to type a password to unprotect it..Any feedback why's that happening? I don't know maybe the code crashed?

I have no idea.