| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Files Displayed As Icons
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
OK. if you need to clear
Ctrl.Delete If this post helps click Yes --------------- Jacob Skaria "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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear Files Displayed As Icons
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| files in folders turning into icons | Excel Discussion (Misc queries) | |||
| how files are displayed in the open file window | Excel Discussion (Misc queries) | |||
| How to increase the number of files displayed when I click 'file' | Excel Discussion (Misc queries) | |||
| How can alert icons be displayed? | Excel Programming | |||
| Bizarre MS EXCEL problem - files will not open via document ICONs | Excel Discussion (Misc queries) | |||
Hybrid Mode
