disable ActiveX controls with VBA

[cgclower]

Hello!

I am trying to disable all the ActiveX controls in my workbook using VBA code. I know I can disable an individual control using this code:

ActiveSheet.MyButtonsName.Enabled = False

But some sheets in my workbook have multiple controls, and not all controls are on all sheets. Is there a way to disable all of these without having to specifically name which controls are on which sheets?

Thanks for the help!

 

[Tom Urtis]

Sounds like you are talking about any activex controls -- no matter the type -- that are embedded onto any worksheet, and you want them disabled. This would do that; set False to True to enable them again.

Sub DisableAllAX()
Dim oOle As OLEObject, ws As Worksheet
For Each ws In Worksheets
For Each oOle In ws.OLEObjects
oOle.Enabled = False
Next oOle
Next ws
End Sub

 

[cgclower]

Thank you! That works perfectly!

Now...heh...I also have some Form Controls in my workbook. I was successfully getting them to disable by running this code:

        'This code will disable all form control buttons in the wkbk
        For x = 1 To Sheets.Count
            If Sheets(x).Buttons.Count <> 0 Then
                Sheets(x).Buttons.Enabled = False
            End If
        Next

But I can't seem to integrate that with the wonderful code you gave me. I was thinking this:

        For Each ws In Worksheets
            For Each oOle In ws.OLEObjects
                oOle.Enabled = False
            Next oOle
            If Sheet.Buttons.Count <> 0 Then
                Sheet.Buttons.Enabled = False
            End If
        Next ws

but that throws an error.

Any thoughts? Thanks!

 

[Tom Urtis]

For efficiency's sake, do these Forms controls tend to be a particular type...example are they all buttons or a combination of buttons and checkboxes It would be easier to specify the types rather than looping through them all. So whatForms controls do you have...there can't be that many.

 

[djreiswig]

For Each ws In Worksheets
    For Each oOle In ws.OLEObjects
        oOle.Enabled = False
    Next oOl
    If [COLOR=Red]ws[/COLOR].Buttons.Count <> 0 Then
        [COLOR=Red]ws[/COLOR].Buttons.Enabled = False
    End If
Next ws

 

[cgclower]

For efficiency's sake, do these Forms controls tend to be a particular type...example are they all buttons or a combination of buttons and checkboxes It would be easier to specify the types rather than looping through them all. So whatForms controls do you have...there can't be that many.

They are all buttons. Anywhere from 0-6 buttons on a sheet.

 

[cgclower]

For Each ws In Worksheets
    For Each oOle In ws.OLEObjects
        oOle.Enabled = False
    Next oOl
    If [COLOR=red]ws[/COLOR].Buttons.Count <> 0 Then
        [COLOR=red]ws[/COLOR].Buttons.Enabled = False
    End If
Next ws

The Buttons.Count line scrolls through the sheets with no buttons perfectly! But, I am getting an error on this line:

ws.Buttons.Enabled = False

"Unable to set the Enabled property of the Buttons class"

Thanks for the help!

 

[djreiswig]

Missing an e in the next, but with that fixed it seems to work fine for me in XL2003.

For Each ws In Worksheets
    For Each oOle In ws.OLEObjects
        oOle.Enabled = False
    Next oOl[COLOR=Red]e[/COLOR]
    If ws.Buttons.Count <> 0 Then
        ws.Buttons.Enabled = False
    End If
Next ws

Edit: Could your sheet be protected? I tried this on a protected sheet and got the same error you mentioned.

 

[cgclower]

Thank you all for your help! I got it to work perfectly. The final hurdle was indeed that my sheet was protected. Here is the major code, in case anyone is interested:

        'Disable all ActiveX and Form controls
        For Each ws In Worksheets
            'Next 3 lines disable ActiveX controls
            For Each oOle In ws.OLEObjects
                oOle.Enabled = False
            Next oOle
            ws.Unprotect (WorksheetPassword)    'need to unprotect every sheet so we can disable Form Controls
            'Next 3 lines disable Form Control BUTTONS, any other Form Control will be active 
            If ws.Buttons.Count <> 0 Then
                ws.Buttons.Enabled = False
            End If
        Next ws