Macro and Protected Sheet Problems

Nitsalet

New Member
Joined
Feb 22, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I have a spreadsheet that suppliers need to complete. In order to standardize the data entered as much as possible, it has several drop down lists and formulas. I need to lock the cells with formulas to prevent them from deleting the content – see cells with blue text on the image. B15-22+ needs to always display a dash. I achieved this by inserting a dash in B15 and using =$B$15 in the cells below B15.
There are also a couple of macros buttons that allow them to insert rows if needed. The macro is written to insert a row and copy the formatting of the row above it. This works fine when the spreadsheet is unprotected, but as soon as I add protection I get a Run-time error as shown in the image. If I click on End, it inserts the row but does not include the formatting. The macro is copied below:

1582489462555.png
1582489486633.png


Users also need to be able to delete rows in two areas (row 15-22+ and 30-36+) which they cannot do when I add protection even though Delete rows is checked under Protect Sheet options. I feel as though these are simple fixes that I am just not experienced enough to know how to do. Any help would be appreciated.
1582489539199.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
I think I understand the first bit of your problem. In my experience VBA cannot edit sheets that you have protected from Excel itself.

Instead you can activate protection from within VBA using a code like this:

VBA Code:
Worksheet.Protect "Password", UserInterfaceOnly := True

You haven't defined any worksheets in your code, so make sure you properly reference it in the code, and choose your own password.
 

Nitsalet

New Member
Joined
Feb 22, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your suggestion, I will see how that goes.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,207
Messages
5,640,844
Members
417,173
Latest member
Tuanphun

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
Top