Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

VBA - Clearing all combo boxes

Hi,

I'm trying to clear the contents of all combo boxes on a specific worksheet. I do not want to delete the boxes just remove all the values for later populating. The code I have below is not working as the object doesn't suppport this method. Any ideas on how to acheive this would be very appreciated

For Each comboObject in Sheets("Main"),OLEObjects
If comboObject.progID = "Forms.ComboBox.1" Then
comboObject.Clear
End If
Next
0
victoriaharry
Asked:
victoriaharry
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:


For Each comboObject in Sheets("Main"),OLEObjects
Dim lListIndex As Long
   
    With comboObject
        .SetFocus
        For lListIndex = .ListCount - 1 To 0 Step -1
            .RemoveItem (lListIndex)
        Next lListIndex
        .SelText = ""
    End With
Next



refer
http://www.vbforums.com/showthread.php?t=384374
0
 
victoriaharryAuthor Commented:
Hi,

Thanks for the quick reply. I get an error on the .SetFocus line. (Not Supported)
0
 
Pratima PharandeCommented:
try without thst

For Each comboObject in Sheets("Main"),OLEObjects
Dim lListIndex As Long
   
    With comboObject
     
        For lListIndex = .ListCount - 1 To 0 Step -1
            .RemoveItem (lListIndex)
        Next lListIndex
        .SelText = ""
    End With
Next
0
 
Michael FowlerSolutions ConsultantCommented:
I have attached an example

Michael
Sub test()
   Dim obj As Object
   For Each obj In ActiveSheet.OLEObjects
      If obj.progID = "Forms.ComboBox.1" Then obj.Object.Clear
   Next
End Sub

Open in new window

0
 
Michael FowlerSolutions ConsultantCommented:
Sorry second time lucky
Sub test()
   Dim obj As Object
   For Each obj In ActiveSheet.OLEObjects
      If obj.progID = "Forms.ComboBox.1" Then obj.Object.Value = ""
   Next
End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now