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

[Brutusar]

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?

 

[6StringJazzer]

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]

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".

 

[6StringJazzer]

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]

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

 

[6StringJazzer]

All gives errors it is more than 20 shapes/buttons witch triggers of different subs.

Wait, you did not start this thread. Are you in the right place? I was addressing posts by @Brutusar

 

[Brutusar]

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]

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?