Solved

Excel:  VBA automatically lock down spreadsheet with exceptions

Posted on 2011-02-25
2
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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