Excel workbook protection - this should be so easy, but...

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with several sheets that I am partly protecting. It should be very easy, but it simply not work, so I must be missing something. The situation is this:

1. Each sheet has one or more shapes that work as button to run VBA code assigned to them.
2. The cells where data need to be changed manually, or by running VBA code, has the "lock hook" removed from the Protection tab of the "Format cells".
3. All shapes/buttons are set as "Locked" in the "Size and properties" tab.
4. The sheets are protected using password.

The issue is:

A. Even when cells are "not locked" it is not possible to run VBA code to manipulate the cells values, however it is possible to manually edit the values.
B. Even after the sheets are protected, it is possible to right click on the shapes/buttons, and move them around, delete or edit.

What am I missing here?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,376
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Even when cells are "not locked" it is not possible to run VBA code to manipulate the cells values
What does "not possible" mean? How are you trying to run the code? Have you tried both clicking the associated button and also using F5 in the VBA development window? Does the code not run? Does it run but not do anything? Does it run but give a runtime error?

It should not be possible to right-click/move/delete/edit shapes/buttons if they are Locked and the sheet is protected. Exactly what shapes/buttons are you using?
 

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am running the code both using the button and F5. In both cases I get a meg box as added in below. It is correct that the sheet is protected, but the cells that the code is going to "work in" (adding or remove values" are not part of the protection, and has the "lock hook" removed from the Protection tab of the "Format cells".
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    8.7 KB · Views: 4

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,376
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
So it is possible to run the code. It's just that the code is giving you an error.

Please show all the code, and indicate which line of code is highlighted when the error occurs.
 

EvertRa

New Member
Joined
Jul 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
All gives errors it is more than 20 shapes/buttons witch triggers of different subs. See example below. All subs are very simple like the example.



Sub ClearPts()

'Purpose :
'Author :
'Description:
'Date : 03Oct21

'ActiveSheet.Protect , UserInterfaceOnly:=True

Pts.Activate

Range("A1:A1000").Clear 'This will be the line that causes the error

Call DropDownList_POLTS


End Sub
 

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
125
Office Version
  1. 365
Platform
  1. Windows
Sorry about the confusion, I was visiting a friend yesterday evening and used his laptop and login at the forum to reply. I did not thing of the login...
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,376
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
What error are you getting? When asking about a runtime error always give the error number and error description.

Which cells are unlocked? Are you sure that cells in column A are unlocked?
 

Forum statistics

Threads
1,175,477
Messages
5,897,664
Members
434,668
Latest member
alteriusquiuus

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