Solved

Hide / Show shape, if worksheet is Locked / Unlocked

Posted on 2015-01-31
4
100 Views
Last Modified: 2016-02-10
Hello Experts,

Please view attached screenshot.

togglehide.PNG
In the screenshot above, the worksheet is locked.  Is there VBA code (I assume that is the only way to accomplish this) that will look at the "Employees" worksheet, and if the worksheet IS locked, then the "Toggle" button is INVISIBLE.  And when the worksheet is unlocked, then the "Toggle" button is VISIBLE.

Every worksheet has it's own toggle button that I will be applying this to.

I assume you will need to know the name of the "Toggle" button, but I don't know where to find it's name, or even if it does in fact have a unique name.  

Thank you in advance for your help!
0
Comment
Question by:Geekamo
[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
4 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40581448
Could you just post the workbook ?
Yes we can do this via VBA
gowflow
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40581560
This is the syntax you require.
    ' for an ActiveX button
    Me.ButtonName.Visible = ActiveSheet.ProtectContents = True
    ' for a Form button
    ActiveSheet.Shapes("ButtonName").Visible = ActiveSheet.ProtectContents = True

Open in new window

Every control (including buttons have a unique name, to find that name right click on the control (you may have to click on "Design" from the developer tab first), the name will be visible in the "Name" drop-down box on the top right corner of your screen, or type
?Selection.Name

Open in new window

and hit ENTER in the immediate window (Ctrl+G to open it)
0
 
LVL 1

Author Comment

by:Geekamo
ID: 40583109
Thank you MacroShadow! :)
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40583292
You're welcome, glad to be of assistance.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

762 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