Clear all activex objects

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,969
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Check below code:

VBA 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

Board Regular
Joined
Feb 25, 2016
Messages
171
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:

VBA Code:
 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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,454
Messages
5,624,846
Members
416,062
Latest member
NIDHYAMARI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top