Clear Files Displayed As Icons

[Joyce]

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

 

[Jacob Skaria]

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]

OK. if you need to clear

Ctrl.Delete

If this post helps click Yes

 

[Joyce]

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]

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

 

[Joyce]

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]

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

 

[Joyce]

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

 

[Joyce]

My apologies for not understanding your previous posting. The type is
embedded.

 

[Jacob Skaria]

OK. Try the below...

Dim Ctrl As OLEObject
For Each Ctrl In ActiveSheet.OLEObjects
If Ctrl.OLEType = xlOLEEmbed Or Ctrl.OLEType = xlOLELink Then
Ctrl.Delete
Else
Select Case TypeName(Ctrl.Object)
Case "CheckBox", "OptionButton"
Ctrl.Object.Value = False
Case "TextBox"
Ctrl.Object.Value = ""
End Select
End If
Next

If this post helps click Yes

 

[Joyce]

This works successfully in clearing the controls, but leaves the embedded
object.

 

[Joyce]

I just tried to test the objects with the Macro sub and it didn't even
recognize my object, so I deleted it and inserted another.

Now the code seems to work perfectly. I will keep test it, but I think it
has done the trick.

Thank you very much!