Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel:  VBA automatically lock down spreadsheet with exceptions

Posted on 2011-02-25
2
Medium Priority
?
311 Views
Last Modified: 2012-05-11
Experts:

I am trying to not have to select every item on my spreadsheet to lockdown while ensuring that my listboxes, buttons, and pictures w/macros are clickable.  Is there away to write a VBA script that will do this?

Please see attached file NOC-Reports-r29.xlsm
0
Comment
Question by:Maliki Hassani
2 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34980819
so you're trying to lock all the cells?  why not just set the lock property of the cells to locked (the default) and the lock property of the controls to not locked, then turn on Protection?  You can lock all cells that have their lock status enabled on demand.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 34982859
Ok - I put a routine in your app to lock everything down to a constant password coded into your app - see public declarations.

I also modified your visible code to simplify it and when things aren't visible they are very hidden (meaning, the user cannot even see which sheets to unhide.

The code unlocks and relocks sheets on chart updating as the code is making a few changes based on listbox selections and updating in the datasheets.

I've done a bit of testing and looking good.

Hope this helps.

Dave
NOC-Reports-r301.xlsm
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question