VBA to hide ActiveX button if Worksheet Protected

[srj1359]

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

 

[neuro0101man]

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]

@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]

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?

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