Clear active x combobox

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
102
Hi All,

would someone help me or point me in the right direction?
i have a active x combobox which is linked to a cell O1 and it returns a listfill range. i use it to search from a long list of suppliers, which is setup a table and it returns what im looking and it works great.

What i would like todo is to be able to clear the search in the active x combobox after i have finished searching, which would be assigned to a button to clear. i can clear the box using a macro, but the dropdown box appears and i have to click inside the combobox to remove it from view. is there a VBA code to clear the box for future searches?

many thanks inadvanced.
Mattless1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
1. Could you upload a sample workbook to a free site such as dropbox.com or google drive & then put the link here?
2. I don't understand, the combobox should be hidden after the searching?
but the dropdown box appears and i have to click inside the combobox to remove it from view.
 
Upvote 0
Hi Akuini,

many thanks for looking at this.

please see link Link to sheet

the combobox is in Cell C8, you start to type anything it will return something. if you click on it then try to clear it and a dropdown box appears under it, but i can't get rid of it unless i click inside the combobox.

thanks,
Mattless
 
Upvote 0
In a standard module put this at the very top of the module, before any code.
VBA Code:
Public DisableEvents As Boolean
Then use
VBA Code:
Sub Clear_Supplier()
'
' Clear_Supplier Macro
'

   DisableEvents = True
   Range("I1").ClearContents
   Range("I4").Select
   ActiveWindow.SmallScroll Down:=-3
   DisableEvents = False
End Sub
and for the combo use
VBA Code:
Private Sub ComboBox1_Change()
If DisableEvents Then Exit Sub
ComboBox1.DropDown
End Sub
 
Upvote 0
Solution
Try this:
VBA Code:
Private Sub ComboBox1_Change()

If ComboBox1.Text <> Empty Then ComboBox1.DropDown

End Sub
 
Upvote 0
I think you can remove this from your Clear_Supplier macro...

Range("I1").Select
Selection.ClearContents

and replace it with this single line of code...

Sheets("Dashboard").ComboBox1.ListIndex = -1

and that should clear cell I1, blank the ComboBox and leave its drowdown closed when you click the "Clear Supplier Search" button.
 
Upvote 0
hI Akuini,

i get an error returned from the code --- Ambigious Name Detected Clear_supplier, mabe its the way im laying out the code i added a new module and entered the boolean code first then added the first code followed by the last one you sent me.

is it the way im laying out the code or is it the name??
 
Upvote 0
Whilst you seem to be talking to Akuini, you seem to be talking about my suggestion.
But this
Ambigious Name Detected Clear_supplier
means you have two procedures with the same name, delete your original code & leave the one I supplied.
 
Upvote 0
What I meant is you need to replace your code:
VBA Code:
Private Sub ComboBox1_Change()

ComboBox1.DropDown
End Sub

with the code on post #5 I gave you
 
Upvote 0
Whilst you seem to be talking to Akuini, you seem to be talking about my suggestion.
But this

means you have two procedures with the same name, delete your original code & leave the one I supplied.
Hi Yes Fluff, So sorry i didn't check the name . just saw the response.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top