Forum Discussion
Solved
Lock shapes in PowerPoint, Word and Excel
Hello!
A relatively new feature in PowerPoint is that you can lock or unlock shapes, although this is only unique to PowerPoint, there is no such feature in Excel or Word.
I want to work with this feature with VBA (macros), I have searched the documentation, but can't find anything to help me. I don't know if VBA properties were not added for this feature or something similar because of the desire to make VBA disappear in the near future.
Does anyone know how to work this feature with VBA please? and will it be possible with VBA to do it with the shapes that I could have in Excel or Word?
Thank you very much
There is no such visual feature in Excel, but the capability of preventing users from moving, editing, or even selecting shapes is present in Excel, and mostly (if not entirely) can be invoked from VBA.
Microsoft Excel terminology is poor in this area, so note that "the ability to lock shapes" is not the same as "the ability to prevent changes to shapes". (It's as though "Locked" means a lock has been installed, not whether that lock is engaged or not.)
Martin is correct about the two-step process -- the changing (if necessary) of Locked status to true, and protecting the worksheet. So see the attached workbook, and let us know why use of Locked status and the worksheet's Protect method (and possibly the Unprotect method) does not satisfy your requirement.
What I do not see is the capability in VBA to set (or unset) the "Lock text" property of the shape I tested. But as this is set by default, perhaps that is not an issue for you.
6 Replies
There is no such visual feature in Excel, but the capability of preventing users from moving, editing, or even selecting shapes is present in Excel, and mostly (if not entirely) can be invoked from VBA.
Microsoft Excel terminology is poor in this area, so note that "the ability to lock shapes" is not the same as "the ability to prevent changes to shapes". (It's as though "Locked" means a lock has been installed, not whether that lock is engaged or not.)
Martin is correct about the two-step process -- the changing (if necessary) of Locked status to true, and protecting the worksheet. So see the attached workbook, and let us know why use of Locked status and the worksheet's Protect method (and possibly the Unprotect method) does not satisfy your requirement.
What I do not see is the capability in VBA to set (or unset) the "Lock text" property of the shape I tested. But as this is set by default, perhaps that is not an issue for you.
- Thank you very much Martin_Weiss and SnowMan55,
I had overlooked that option in the shapes, I was expecting to see something similar to what PowerPoint has, but now I know how I could mimic that feature in Excel.
Thank you very much, thank you very much.
in Excel the lock mechanism always consists of two steps:
1. changing the object property (locked / unlocked)
2. activate the sheet protection (menu "Review | Protect sheets")
Also for objects like shapes, there is a "Locked" property that you can change. Right-click the shape, and select "Size and Properties..."
If you use the macro recorder, you get this code:
So you can change it to msoTrue to set it to "Locked"
By default, every object has this property set to "Locked", but it only gets activated if you switch on the sheet protection:
In VBA:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
I'm not sure if this answers your question.
- It seems that the images I upload are not appearing, sorry.
I am uploading the image as an attachment.I did saw the images and you are right, this functionality is not available in Excel. But I did not know if you were aware of the general lock mechanism of shapes in Excel, that's why I posted my alternative solution.
But I agree, such an easy way to lock/unlock that you showed in Power Point would be appreciated in Excel as well 🙂
Hi Martin_Weiss, thank you very much for your time.
I apologize, I realize that the image was not uploaded and perhaps that is why it was not understood, sorry. I share the image.I am referring to the ability to lock shapes (as in the image), currently it can only be done in PPT, but I don't know how to do it with VBA, I don't find it in the documentation.
In Excel we can do something similar, but only referring to not move in case I make change in the rows or columns as its size, what I want is that you can't do anything with shapes (as can be done now with the shapes in PPT, as in the image).
Thank you very much friend Martin.
