Clear all activex objects

[Mikeymike_W]

Hi there,

I was wanting to clear all of the activex objects on my sheet but am unsure how to do it.

I tried the code below but it doesn't work. Currently i am using textboxes and comboboxes but thought it would be good to cover radiobuttons and checkboxes too just in case they're added later.

Thanks in advance for any help you can give,

Mike

Dim oCtrl As Control
    For Each oCtrl In ActiveSheet.OLEObjects
        Select Case TypeName(oCtrl)
            Case "TextBox", "ComboBox"
                oCtrl.Value = ""
            Case "OptionButton"
                oCtrl.Value = False
        End Select
   Next oCtrl

 

[ranman256]

I get errors with oCtrl.Value =,
yet: oCtrl=myValue works.

option buttons are not true /false , they can have any number. (yes true/false is a number)
option btns are set by the frame. Are you using a frame to hold the option buttons?

checkboxes are true/false

 

[Saurabhj]

Hi,

Check below code:

Sub clearfields()
    Dim ole As OLEObject

    For Each ole In Worksheets("Sheet1").OLEObjects
        ole.Object.Value = ""
        If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
            ole.Object.Value = False
        End If
    Next ole

End Sub

 

[Mikeymike_W]

I get errors with oCtrl.Value =,
yet: oCtrl=myValue works.

option buttons are not true /false , they can have any number. (yes true/false is a number)
option btns are set by the frame. Are you using a frame to hold the option buttons?

checkboxes are true/false

Hi Ranman,
At the moment i have textboxes and comboboxes.
I dont have optionbuttons or checkboxes but just wanted the code to cover these in case they get added in the future.

I managed to get working code that does the job, thank you so much for your reply and help though.

Heres the code i used:

 Dim oCtrl As OLEObject
 Dim shp As Shape
 
  For Each oCtrl In ActiveSheet.OLEObjects
    If TypeOf oCtrl.Object Is MSForms.TextBox Or _
       TypeOf oCtrl.Object Is MSForms.ComboBox Then
        oCtrl.Object.Value = ""
    ElseIf TypeOf oCtrl.Object Is MSForms.OptionButton Then
        oCtrl.Object.Value = False
    End If
  Next oCtrl