Excel VBA ActiveX Series #4 Combobox - Drop down you can resize and move. Fill with Custom criteria

Рет қаралды 98,047

Күн бұрын

📊 Free Workbooks: www.excelvbaisfun.com/links?u...
🥷Join Excel Ninja Pro: www.excelvbaisfun.com/ninjapr... Months FREE On Annual Plan Auto Applied)
🥷Excel Ninjas FB Group: www.excelvbaisfun.com/xlninjas (Free downloads, Trainings, Live Q&A and more)
A Combobox is like a drop-down list, except you may also type custom things beyond the scope of the list. You can move them all around your worksheet and resize them easily. You can also fill them any way you want using the ADDITEM method. For example, to fill a simple yes or no combobox:
Sub FillCmbOne()
Combobox1.Clear 'to start from scratch.
Combobox1.AddItem "Yes"
Combobox1.AddItem "No"
End Sub
Fantastic Developer Tools:
🔒 Transform Any Excel File Into A Locked EXE: www.excelvbaisfun.com/secure-... (25% off with code ‘25OFF’)
🟡 Create Custom Installers: www.excelvbaisfun.com/custom-...
👋 Business Inquiries, Consulting, Comments, etc: www.excelvbaisfun.com/contact/

Пікірлер: 38

@OptionGal 11 жыл бұрын

been trying to figure out how to dynamically populate a ComboBox so that I can have a routine run when the combobox changes. I was close to giving up when I came across this video.... oh so helpful! Your demo was perfect... thanks a bunch!!

@thomashall7285 7 жыл бұрын

Hey Daniel I was wondering why every time I try to type my named range into the ListRillRange in the combo box properties it is being deleted as soon as I hit return or Tab. Thank you in advance for your help.

@tEzZz3168 10 жыл бұрын

Thanks Dan! Been Greatly Helpful!!

@ExcelVbaIsFun 11 жыл бұрын

Wow that makes my day. Lemme know if you have questions, glad to help! Dan

@gopalakrishnan565 11 жыл бұрын

good tip, can you please update for similar use of combo box in user form.. thank you

@sasavienne 5 жыл бұрын

Thank you for making VBA easy for me to understand and cope with. You get: 🌟 🌟 🌟 🌟 🌟

@Thunderin11 5 жыл бұрын

How can you detect if the embedded ActiveX ComboBox is expanded (dropped down) or not?

@jessicaadele 8 жыл бұрын

Hi, my active x box keeps expanding (gets larger) every time I select something from the drop down list. Can you help?

@leokaz 11 жыл бұрын

Very explicit explanation! Thanx a lot!

@ji5378 10 жыл бұрын

Can you put a default name is the combobox? Something like "Pick Your Team Here". After you pick your team the team name is displayed in the combobox. Thank you

@caribeiro77 10 жыл бұрын

In order to take this example a step furder, what if we type something in the combobox that ist's not in the list and a msgbox pop's up with message: "Item not in actual list, do you want to add new item to the list", and another macro run that order and add that new item to the list....For examle if we type Coca-cola in the combobox and them item is added to the list....That woul'd be cool...

@ExcelVbaIsFun 11 жыл бұрын

Thanks Leon, glad it's helped! Dan

@tEzZz3168 11 жыл бұрын

Would that dynamic combo box be working if the list being updated in a different workbook? Thx fr dan

@ExcelVbaIsFun 11 жыл бұрын

just filmed, will try to upload tonight. Thanks gopal

@ExcelVbaIsFun 10 жыл бұрын

if it does, i don't know how to reference it. I made a dynamic list in workbook A, then tried to reference it in wb B. I tried this: Made the Rowsource in the combobox equal to this: [testa.xlsm]Sheet1!$A$2:$A$8 It worked, but only while the other wb was open. I also tried the named range as the Rowsource on the Userform combobox, called "LIST": [testa.xlsm]Sheet1!LIST This also worked, but only until other wb was closed. Not a great solution. dan

@ExcelVbaIsFun 10 жыл бұрын

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

@harsh0520 8 жыл бұрын

Hi I keep getting System error for the ComboBox.clear statement. I googled and found that many are unable to resolve this issue. It working in your demo. Can you suggest what is causing this error?

@nuhzakir8896 9 жыл бұрын

Thank you so much.

@gerardvaneggermond9967 4 жыл бұрын

Hello, maybe you can help me, I also want to make a dropdown list but this list must also depend on the worksheet to be searched for, and a second list that depends on the first one, in Excel the list is successful, but can it also in VBA? Can you help me?

@ExcelVbaIsFun 10 жыл бұрын

and in a worksheet (non userform combobox), you'd use ListFillRange instead of RowSource. Sorry, forgot what video this was referring to. Dan

@ExcelVbaIsFun 10 жыл бұрын

Jason, you can put whatever you want in the combobox, you can put me.cmbSweets = "Pick Your Team Here". As for when you pick your team, do you want a different combobox to populate with the list from that particular chosen team? Here's a video i made for that: Two Comboboxes VBA Userform - One Controls the Other - Excel VBA Is Fun

@rialphnocon299 4 жыл бұрын

Hi Dan, is it possible to do the 2 ComboBoxes which control the other in a worksheet using listfillrange or is there an alternative? Thanks

@krn14242 11 жыл бұрын

Thanks, cool.

@ExcelVbaIsFun 10 жыл бұрын

Very welcome!

@NetherVoiD 5 жыл бұрын

Bad thing about combo box you cant stick it inside a cell like data validation. You have to be like an engineer to make combobox stick to a cell.

@hari02081025 10 жыл бұрын

Hi Daniel, For combobox dropdown list, i have used ComboBox1_DropButtonClick and it is showing all items as expected without using any command button. But, whenever i select the dropdown, the list is getting expanded continuously. Let me know, if you have solution for this without using any command button. Please check the below code: Private Sub ComboBox1_DropButtonClick() Dim Cell As Range 'Me.ComboBox1.Clear For Each Cell In [MyList] Me.ComboBox1.AddItem Cell Next Cell 'Me.ComboBox1.Clear End Sub

@ExcelVbaIsFun 10 жыл бұрын

take the comment (apostrophe) away from the first instance of me.combobox1.clear. That shouldn't be commented out. THanks! Dan

@hari02081025 10 жыл бұрын

ExcelVbaIsFun I have already tried this and when I select an item from drop down list, the item is getting cleared. Thank you!

@ExcelVbaIsFun 10 жыл бұрын

hari, What's your goal? Are you needing the list re-populated frequently or just when the workbook opens? Or less frequently, say when something is triggered or a button is clicked? Send me your wb if you'd like and I can take a further look. excelvbaisfun@gmail.com

@hari02081025 10 жыл бұрын

ExcelVbaIsFun i have mailed you wb. Please check.

@ExcelVbaIsFun 10 жыл бұрын

hari prasad just put "myList" in the ListFillRange since it's a constant list. Dan