Disable shapes when Workbook is protected VBA

[julhs]

I have several shapes on the worksheet that have macros assigned to them.
I want to specifically disable/deactivate those SHAPES while the sheet is PROTECTED.
Reason being is that at the moment when the sheet is PROTECTED (via VBA) and I then subsequently click on one of the shapes it causes the assigned Sub to not run/freeze/bug out.
So I thought the best approach would be to disable/deactivate those shapes while the Sheet is PROTECTED.
There are other "btn" shapes on the sheet, if they can be disable/deactivate on mass then it would avoid the need to name them individualy
But to date none of my attempts have worked.
This is my last failed attempt:

Sub ProtectSheet()
ActiveSheet.Protect ActiveSheet.Shapes("btnSelectTheSubToRun").ControlFormat.Enabled = False
End Sub

 

[mumps]

Try:

Sub shapes()
    Application.ScreenUpdating = False
    Dim shp As Shape
    If ActiveSheet.ProtectContents = True Then
        For Each shp In ActiveSheet.shapes
            shp.Visible = False
        Next
    Else
        For Each shp In ActiveSheet.shapes
            shp.Visible = True
        Next
    End If
    Application.ScreenUpdating = True
End Sub

 

[julhs]

Thanks for response.
Your suggestion is not really the route I was looking to take.
It does work to hide ALL the shapes on the Sheet, but I would have to adjust/rewrite it to apply to ONLY specific shapes.
My observations of the code as it stands is; yes ALL shapes are hidden but its leaving ALL the Comments showing/expanded.

As an alternative I’ve been playing around with adding a Yes/No MsgBox in each of the Subs assigned to the specific shapes (that I was looking to disable) to have the option to Unprotect the sheet and run the Sub or not. That way I could at least avoid the Subs running and them subsequently freezing/bugging out when the sheet is Protected.

Have you any further thoughts?

 

[mumps]

I would have to adjust/rewrite it to apply to ONLY specific shapes.

If you post a list of the shape names that you want to hide, the macro can be modified to hide them.

but its leaving ALL the Comments showing/expanded.

What do you mean by Comments?
It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do using a few examples from your data.

 

[julhs]

The adjustments/rewrite is not a problem for me.
My meaning of “Comments” is the red popup triangle box in the top right hand corner of a cell (they have been given a newer name but I can’t remember what it is)
I appreciate to a degree that an uploaded shared file may be of of SOME help; but it is huge with multiple command buttons with macros assigned, multiple change events and masses of condition formatting etc etc, 99.999 % of which is not relevant to this query.

I will try and compile an xl2BB that gives you more to work without it being ridiculously overloaded, failing that I will just try and better describe the problem.
Either way I will come back to you tomorrow.
Many thanks
Juhls

 

[julhs]

I have spent time in trying to produce an acceptable xl2BB, but it got ridiculous!!
The key query is how to DISABLE the button/shape and the subsequently code that’s attached to it IF the WorkSheet is Protected.

But i’ve been messing around with an alternative approach by adding this to the Main/Parent sub;

If ActiveSheet.ProtectContents = True Then
Call UnProtectSheet

This is the Called/Child Sub

Sub UnProtectSheet()
Application.ScreenUpdating = False
If MsgBox("The Sheet is PROTECTED. Do you want to continue and run the Main Sub ?", _
vbQuestion + vbYesNo) = vbNo Then
Exit Sub
Else
ActiveSheet.ProtectContents = False
Application.ScreenUpdating = True
End If
End Sub

Problem I cant seem to get over at moment with the alternative approach is;
If MsgBox selection is NO in the CHILD sub, it doesn’t EXIT the Main Sub, it currently just continues from the point of the “Call” event in the Main sub regardless of answer being “No” (in the Child sub)

 

[mumps]

Exit Sub

This exits the current sub not the one called.

 

[julhs]

I am sorry for being so dim, would have thought I’d learnt enough by now to deal with this!!
On basis of what you have said, how do I EXIT the the MAIN sub if the MsgBox answer in the CHILD sub is NO?

 

[mumps]

Replace this line of code in your main sub:

Call UnProtectSheet

with this code:

If MsgBox("The Sheet is PROTECTED. Do you want to continue and run the Main Sub ?", vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
Else
    ActiveSheet.ProtectContents = False
    Application.ScreenUpdating = True
End If

This way the Exit sub command will exit your main sub.

 

[julhs]

Altered it but getting;

Run-time error ‘1004’ Unable to set the Protect Contents property of the Worksheet class

on this line,

ActiveSheet.ProtectContents = False