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

Gtasios4

Board Regular
Joined
Apr 21, 2022
Messages
80
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi Joe4,

Thanks for your kind response. I have the below VBA in Sheet1 & Workbook respectively but the problem still remains.

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


What about deleting a row on protected sheet as described in my 1st thread?

1650614725961.png
 

Attachments

  • 1650614710775.png
    1650614710775.png
    5.5 KB · Views: 3
Upvote 0
Are they protected with a password or not?

Do you have any other event procedure code, like maybe "Worksheet_Change", "Worksheet_SelectionChange", or "Worksheet_Calculatge" procedure code in any of your worksheets?

Have you applied any formulas or Conditional Formatting to ALL rows of your worksheet?

If you hit CTRL+END on your worksheet, what cell does it jump down to?
 
Upvote 0
Are they protected with a password or not?

Do you have any other event procedure code, like maybe "Worksheet_Change", "Worksheet_SelectionChange", or "Worksheet_Calculatge" procedure code in any of your worksheets?

Have you applied any formulas or Conditional Formatting to ALL rows of your worksheet?

If you hit CTRL+END on your worksheet, what cell does it jump down to?
Yes I have a worksheet change code event in sheet1, because i have dependent drop down lists

1650640510334.png


Apart from that in my quotation tool I have a conditional formatting in L column.

As for CTRL+END it jumbs into AF95 on Sheet1.

1650640574385.png
 
Upvote 0
What happens if you try it manually?
Are you able to do it then?

So manually unprotect your sheet, and try manually inserting a row, and copying your one row to the new one.
Does that work, or do you get some sort of error (if so, what does it say)?

Also, do you have merged cells in your workbook?
Does either the row you are copying from and the row you are copying to contain these merged cells?

Merged cells are just about this worst feature of Excel, and should be avoided, if at all possible.
They cause all sorts of issues for things like VBA, sorting, etc.
 
Upvote 0
What happens if you try it manually?
Are you able to do it then?

So manually unprotect your sheet, and try manually inserting a row, and copying your one row to the new one.
Does that work, or do you get some sort of error (if so, what does it say)?

Also, do you have merged cells in your workbook?
Does either the row you are copying from and the row you are copying to contain these merged cells?

Merged cells are just about this worst feature of Excel, and should be avoided, if at all possible.
They cause all sorts of issues for things like VBA, sorting, etc.
When I unprotect the sheet every vba code works fine. But when the sheet is protected then errors arise such as the above depicted.
 
Upvote 0
When I unprotect the sheet every vba code works fine. But when the sheet is protected then errors arise such as the above depicted.
If that is the case, then unprotecting the sheet via VBA code before performing the actions should work.

When you manually unprotect it, do you have to use a password?
 
Upvote 0
If that is the case, then unprotecting the sheet via VBA code before performing the actions should work.

When you manually unprotect it, do you have to use a password?
Manually unprotected I ve tried both with and without password.

So the below VBA codes can’t help?

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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top