Clear active x combobox

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
94
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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.
 

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
94
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
Private Sub ComboBox1_Change()

If ComboBox1.Text <> Empty Then ComboBox1.DropDown

End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
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.
 

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
94

ADVERTISEMENT

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??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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
 

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
94
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,690
Messages
5,637,841
Members
416,985
Latest member
mrindira

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
Top