When I run this, it keeps stopping at the checkbox line, where I've put (THIS
LINE) with the same run time error.

Sub Clear_Controls()
Dim Ctrl As OLEObject

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False (THIS LINE)
End If
Next Ctrl
End With

'Clear radio buttons
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "OptionButton" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

'Clear text boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "TextBox" Then
Ctrl.Object.Value = ""
End If
Next Ctrl
End With

'Clear embedded files
With ActiveSheet
For Each Ctrl In .OLEObjects
If Ctrl.OLEType = xlOLEEmbed Or Ctrl.OLEType = xlOLELink Then Ctrl.Delete
Next Ctrl
End With

End Sub

"Jacob Skaria" wrote:

I would suggest you to read my earlier post again on OLeTypes..and try the
below code..

With ActiveSheet
For Each Ctrl In .OLEObjects
If Ctrl.OLEType = xlOLEEmbed Or Ctrl.OLEType = xlOLELink Then Ctrl.Delete
Next Ctrl
End With

If this post helps click Yes
---------------
Jacob Skaria


"Joyce" wrote:

They are Object (as in Object 20, Object 21, etc.)

I tried the following, but got the run time error again:

'Clear embedded files
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "Object" Then
Ctrl.Object.Delete
End If
Next Ctrl
End With



"Jacob Skaria" wrote:

OK Try getting the OLEType ...which should return the below for each type..

Sub Macro()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If Ctrl.OLEType = xlOLEEmbed Then MsgBox Ctrl.Name, , "Embedded"
If Ctrl.OLEType = xlOLELink Then MsgBox Ctrl.Name, , "Linked"
If Ctrl.OLEType = xlOLEControl Then MsgBox Ctrl.Name, , "Control"
Next
End With
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Joyce" wrote:

Hi Jacob,

Thanks very much. I tried this, but got the same run time error as when I
tried it myself. If I add the embedded file as icon, I get:

Run time error 1004 unable to get the object property of the ole objject.

I then removed the object and reinserted it and re-ran the code. This time
it cleared the other objects, but left the Word file that I'd added as an
icon. (No error message?)

Thank you.

"Jacob Skaria" wrote:

Dim Ctrl As OLEObject

With ActiveSheet
For Each ctrl In .OLEObjects
Select Case TypeName(ctrl.Object)
Case "CheckBox", "OptionButton"
ctrl.Object.Value = False
Case "TextBox"
ctrl.Object.Value = ""
Case Else
MsgBox "Do whatever with " & ctrl.Object.Name
End Select
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"Joyce" wrote:

Hello,

I have a variety of controls on my worksheets, including checkboxes, radio
buttons, text buttons and files displayed as icons.

I have code that clear these when the user is ready to begin from scratch
again.

The following code works fine, but I'm not sure how to add the code for the
icons. These will probably be Word files or pdf for the most part.

Thanks.

Dim Ctrl As OLEObject

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

'Clear radio buttons
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "OptionButton" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

'Clear text boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "TextBox" Then
Ctrl.Object.Value = ""
End If
Next Ctrl
End With