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

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


**Limited Offer** Learn to Make Excel Do Your Work For You with The Ultimate Excel Programmer Course – Get the Full 9+ Hour Premium Course for 75% off using coupon code: https://www.udemy.com/ultimate-excel-... Click Now to Order and get Lifetime Access to Course, Workbooks, Updates and Support!
------------------------
Create Your Own Barcode Lookup System Using Excel VBA. Learn to Make Your Own Barcode Labels the Easy way and have fun with Barcode Scanners to Automate your Workflow! In this project-driven Course, you’ll learn to Build your own Custom Inventory System with Step-By-Step video instructions. This goes in depth into some advanced Userform strategies that you can use for your other projects to come. A great weekend study! YouTube Subscibers only pay one forth! Get your 75% off Coupon here: https://www.udemy.com/barcodes-excelv...
------------------------
Too busy to Learn Piano? Use my Secrets and Play Your Favorite Songs Today! Did you know; You can learn 4 basic chords in about 5 minutes and instantly be able to play millions of songs. Seriously, check this course out. 75% off for my YouTube Friends! Click here: https://www.udemy.com/play-piano-by-e...

For Articles, Updates and to Contact me for Consulting or just to drop me a line, please visit http://www.ExcelVbaIsFun.com

Here is my link to download my FREE workbooks, past present and future: https://www.dropbox.com/sh/nwc5trm2xw...

------------------------
Playlists:
Excel Vba Basics http://www.youtube.com/watch?v=AIhKNN...
Tips N Tricks https://www.youtube.com/playlist?list...
Userforms https://www.youtube.com/playlist?list...
Sample Excel Programs https://www.youtube.com/playlist?list...
Events Series https://www.youtube.com/playlist?list...
Activex Controls https://www.youtube.com/playlist?list...
------------------------

Grab an Excel Nerd T-Shirt or Hoodie:
http://ExcelVbaIsFun.spreadshirt.com/

------------------------
Interact and Follow me on:
LinkedIn: http://www.linkedin.com/in/danielcstrong
Facebook: https://www.facebook.com/Excelvbaisfun/
Twitter: https://twitter.com/ExcelVbaIsFun
Google Plus+: http://www.google.com/+ExcelVbaIsFun


'Royalty Free Music by http://audiomicro.com/royalty-free-mu...

---

Closed Caption:

hey welcome back youtubers dinner
Stronghammer with another excel vba is
fun exciting lesson
ok so you know that with a regular cell
you can fill a dropdownlist i'm sure you
know this if you've been watching excel
is fun
my girl is awesome videos okay so you
can create one of these by adding alt
dl4 data validation and you would select
a list and you would put the name of the
either range either a 3 through a 7 or
in this case I named that range to be
called sweets list
ok so why in the world would you want to
put a combobox that from the activex
controls well let's find out let's go
into design mode from the Developer tab
whoops click on insert and we're going
to do this funky-looking think of our
combo box
what is that I'm sure you've seen in a
bazillion times it is simply a drop-down
list just like you see on websites
everyday drop-down menu that's what a
combo boxes want to give this name CMB
for combo box and you can call me
anything you want i just put these
things at the beginning so i can
remember what they are
C&B sweets can enter this is called CMB
sweets that's what my combobox is called
now what
so what's the point well let's say you
didn't only want it so you didn't want
to fill it with this list or you want to
fill it with that and append that with a
few custom things that weren't on your
regular list or you only want to say you
only wanted it you wanted to go through
these and only take the one that ends
with the letter S or only take the one
that's five characters or more or
whatever this is dynamic this you can
only have whatever is in the list
nothing more nothing less this is
dynamic you can run a macro to fill your
this box are you calming excuse me so
let's go ahead and double-click on the
combo box and get started
ok let's see I'm going to take these
other macros out because they were for
the other sheets that i just copied all
right
CMB sweet change you notice whenever you
click on that will click on a combobox
by default it's not a click
it's a change event because they assume
you're going to click on the drop-down
menu and change it to something else
there's other things you can do but
let's go over the change one this is the
most useful one
okay i guess i was i'm i'm still in
userform still here in my mind I'm
thinking that there isn't an initialize
but there isn't
now try talking to pressure but let's
put a button just for now right-click
properties and give the caption a quick
click here thing i will say click to
fill ok with that with a userform as
soon as these reforms open you can do an
initialize thing where you can I guess
we could do that with the sheet activate
our workbook got workbook open event
just do a button make it real easy as
soon as I click this button this this
combo box is going to fill up with our
criteria so we'll go through each cell
in the suite list here
double click here so we're going to
we're going to fill our combo box how we
gonna do we're gonna say for each we've
talked about this for each cell in what
was our it was calls sweets list
for each cell and sweet sister but you
need to do it next cell
okay for every time you do a for each or
4x equals blah blah whatever you put
here is the variable this one just
called sell you need to say next cell so
it can keep looping through all of them
for each cell in our list what we're
going to do with each one we're going to
say we're going to take the last letter
and see if it's a letter s just for fun
if right if the rightmost character kind
of 10 k equals letter S then I guess
continue about how about if it's not an
S we just won't let us ignore it
no that's wrong again if it equals s
then we'll tell it what we wanted to do
we're going to say . CMB sweet that's
our combo box . add item add item is the
big one here hit space and you tell them
what you wanted to be so what we want to
add item to be whatever's in our
variable the cell or cell . value would
work too well just say add item whatever
the cells says and if okay i don't
actually do it scary and you click the
button in let's run through it once well
design mode and when i click to fill us
i clicked right here in the grey and
that put a stop marker right there
so when i hit f8 debug now the first
cell and our list is cake and our salads
yeah that's right
so if the right-most one not two or
three if they're rightmost one character
of the thing called cell
is s then well cake doesn't end with the
letter S so I skipped over that one
how about doughnuts oh yes so now CMB
not sweet is going to add the item which
is doughnuts so it's added and you can't
see but it's in there
let's go to the next one how about milk
duds
yep we're going to add that one we're
going to add frosted flakes and soda
does not internet so it skips over that
one in sub ok so let's see oh look at
their don't smoke does frosted flakes
check that out huh
now but you'll notice it's going to
happen if i click it again it's going to
keep adding items it's not going to
clear it out and then Adam so I only
click the button again and you'll see
what happens if i hit f5 to complete
that you're going to see that it is
actually added all those items again
Oh what we do well there's something
cool called clear that clear so let's
click here every time we click on that
going to say right before this loop runs
were going to say me . CMB sweets .
clear we want to clear out all the
previous entries and then we want to
refresh rerun this macro here so let's
do that
so take it off design will click the
fill don't smoke does Russell flakes
click again it cleared it and then reran
the criteria how about let's let's open
that up again let's say if the rightmost
thing is to letter e now click to fill
who it only has cake
Wow school okay well so what else did we
say we're going to do try to River ok
how about if the length of whatever is
more than
more than 70 characters so also have 11
again let's get back at it for each cell
and suites list about let's say if the
length groups if the length of the cell
mercy if the links of the cell is
greater than 7 we can also say greater
than or equal to $OPERAND but will say
if the length of the cell is greater
than seven then add that I don't but it
also clears it beforehand
ok let's try that will get milk duds and
frosted flakes it's closed the only ones
that are greater than 7 how about less
than seven have a less than or equal to
7 could fulfill only these ones are less
than or equal to 7 digits long
what if i add a bunch of spaces to the
word take oh you notice it's not there
so account spaces as a character
alright thanks for tuning in will check
out some more awesome things here oh
well however before i do sign off i want
to tell you we talked about what's the
you know other benefits besides the fact
that you can customize how to fill in
your combo box well check this out
you can move them around you can put
them anywhere you want you can make them
as Biggers little are weird shaped as
you want you can move them in front of
tax by behind text just like just like
an image really so I'm very interesting
and they retain the the value that you
fill them with last time even if you're
moving around and put them in design
mode whatnot they retain whatever you
fill them with last time so and you can
do with that so thanks for tuning in