VBA to hide ActiveX button if Worksheet Protected

srj1359

New Member
Joined
Mar 5, 2015
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
Hi there!

I have a document with two buttons:

1. "Edit Discount"
2. "Stop Editing and Recalculate"

What I want to happen is for the sheet to be protected when the user first accesses it and only the "Edit Discount" button visible. When the user clicks the button, the sheet is unprotected and the "Stop Editing and Recalcuate" button becomes visible. After the user makes any necessary changes while the sheet is unprotected, they can then click "Stop Editing and Recalculate." After clicking that button, it will recalculate and the sheet will be protected and only the "Edit Discount" button visible.

I've been able to do most of this, but the part that I keep struggling with is the visibility of the button. At the end of the macro in the "Edit Discount" button it says:

If Worksheets("Quote Summary").Unprotect = True Then
ActiveSheet.Buttons("Button 17").Visible = False
Else
ActiveSheet.Buttons("Button 17").Visible = True
End If
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

neuro0101man

Banned - Rule violations
Joined
Aug 17, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
so what exactly is the issue? your code is throwing an error because the sheet is protected once again when you try to run it? or is the code simply not working? there are 2 types of controls in excel's interface => activeX controls and regular form controls. I do not know of any other types of controls available.
 

srj1359

New Member
Joined
Mar 5, 2015
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
@neuro0101man I'm not getting any kind of error message, it's just not doing what I would like for it to in terms of the "Stop Editing and Recalculate" button only being visible when the user clicks the "Edit Discount" button.
 

neuro0101man

Banned - Rule violations
Joined
Aug 17, 2020
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
ummmmm.....you say: ""in the edit discount button, I have this code....

[code here]

but before that you said: After the user makes any necessary changes while the sheet is unprotected, they can then click "Stop Editing and Recalculate." After clicking that button, it will recalculate and the sheet will be protected and only the "Edit Discount" button visible.

i'm kind of guessing you are looking for something like this behind the ""stop editing and recalculate"" button?
Code:
If Worksheets("Quote Summary").Unprotect = True Then
ActiveSheet.Buttons("the button name that is NOT the edit discount button here").Visible = False
ActiveSheet.Buttons("edit discount button name here").Visible = true
Else
ActiveSheet.Buttons("the button name that is NOT the edit discount button here").Visible = True
ActiveSheet.Buttons("edit discount button name here").Visible = false
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,586
Messages
5,625,658
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top