asked on

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

Pratima

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

victoriaharry

ASKER

Hi,

Thanks for the quick reply. I get an error on the .SetFocus line. (Not Supported)

Pratima

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

Michael Fowler

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

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial