Workbook_Open event to set value of all buttons in workbook to false-CodePudding

I have a workbook with multiple sheets with command and toggle buttons. I am trying to create Workbook_Open event that will set value of all buttons to false. Code below returns error: "Compile error: Method or data member not found" and highlights Private Sub Workbook_Open() line, so I don't know where to look for error. How to fix it or achieve it other way?

Private Sub Workbook_Open()

Dim OleObj As OLEObject
    For Each OleObj In Sheets.OLEObjects
        If TypeName(OleObj.Object) = "ToggleButton" Or TypeName(OleObj.Object) = "CommandButton" Then
            OleObj.Object.Value = False
        End If
    Next OleObj
    
End Sub

CodePudding user response：

You need to loop through each sheet in turn, and you don't need to process commandbuttons - for example:

Private Sub Workbook_Open()
Dim ws as worksheet
For each ws in activeworkbook.worksheets
    Dim OleObj As OLEObject
    For Each OleObj In ws.OLEObjects
        If TypeName(OleObj.Object) = "ToggleButton" Then
            OleObj.Object.Value = False
        End If
    Next OleObj
Next ws
End Sub

CodePudding user response：

Give this a go:

Private Sub Workbook_Open()
    
    Dim OleObj As OLEObject
        For Each OleObj In Sheets.OLEObjects
            If OleObj.Name = "ToggleButton" Or OleObj.Name = "CommandButton" Then
                OleObj.Object.Value = False
            End If
        Next OleObj
        
    End Sub