Which ComboBox?

[Minitman]

Greetings,

I noticed that there are three different ComboBoxes available.

The one in the Visual Basic Editor, the one in View>Toolbars>Control
Toolbox and the one in View>Toolbars>Forms.

I was wondering if anyone could tell me what are the pros and cons of
each type?

I would really appreciate any comments on this question.

-Minitman

 

[Gord Dibben]

The combobox from View>Toolbars>Control Toolbox is same one as Toolbox in VBE.

This type of combobox is much more customizable and versatile than the one from
the Forms Toolbar.

Draw one of each on your sheet to see the difference when you right-click.

With Toolbox combobox right-click and "Properties" will give a host of options
not found on the Forms combobox.


Gord Dibben MS Excel MVP

 

[JLGWhiz]

Just to add a little history to Gord's excellent explanation, the one from
the forms toolbar is a hand-me-down from the early Excel days, pre xl95. I
was using that version until we go an update in 97 or 98 and again in 2000.
I don't remember exactly when I personally realized that there were two
versions during that period, but when I did, I quit using the forms version.
Same with the other controls. If it is in the toolbox, that is the one I use
for maximum flexibility and utility.

 

[Minitman]

Hey Gord,

Thanks for the reply.

When I first wrote my code, I tried to use the Toolbox ComboBox but
could not find the RowSource property so went with the Forms ComboBox.
Like you said it does not have as many properties. But one property
that it does have is the Link Cell property, which returns the row
number of the item chosen. This is very handy.

The Toolbox ComboBox does not have a RowSource property, but I
discovered today that it has a ListFillRange property which does
almost the same thing.

As for the Forms ComboBox properties, under the Format Control>Control
there are two drop downs, one for Input Range (Row Source) and one for
Cell Link (which returns the row number of the chosen item in the
Input Range).

The Toolbox ComboBox also has a LinkCell property but all it does is
show what is in the chosen cell, not which row it is.

Although the Toolbox ComboBox has more properties the Forms ComboBox,
the Forms ComboBox has that Cell link that I was able to use.

All of the other cell formulas are Offsets based on that row number in
the linked cell. I am trying to get away from VLOOKUP's.

If I could figure out how to get the row number of the chosen item, I
would gladly switch from the Forms ComboBox to the Toolbox ComboBox.

Any ideas? Would it help if I posted my code?

-Minitman

 

[Dave Peterson]

If those values are unique in the listfillrange, you could use =match() to
return the row number in that range.

=if(a1="","",match(a1,b1:b10,0))

Where A1 is the linked cell and B1:B10 is the listfillrange.

 

[Dave Peterson]

Just some more info...

Personally, I like the controls from the Forms toolbar when I put them on a
worksheet. They seem better behaved than the controls from the Control Toolbox
toolbar. Less things go wrong -- especially when there are lots of controls on
the worksheet.

Another nice thing about the controls from the Forms toolbar is that I can
assign the same macro to any of the controls. So if I have a bunch of
checkboxes or buttons that do almost the same thing, I can create one macro and
do a little branching based on what checkbox/button was clicked. Then do the
common work.

Besides all the properties and events that I can use with controls from the
control toolbox toolbar, it also makes life a bit simpler to copy sheets between
workbooks. The code that I use for these controls is placed under the
worksheet. That means that code will travel with the worksheet when it's moved
or copied.

On the other hand, the code for macros for those controls from the Forms toolbar
is located in a General/Regular/Normal (different names for the same thing!)
module. That means that moving/copying a worksheet with a control that has a
macro assigned to it will also have to have that macro copied to a general
module in the receiving workbook's project.

This isn't too difficult to manually, but with all the security excel has added
over the years, maybe be impossible to do in code.

 

[Minitman]

Hey Dave,

Thanks for the reply and the good insight, it is greatly appreciated.

I find only one thing wrong with the Forms ComboBox. I can't type
into it, I can only scroll though it. With 3000 records to scroll
though, it takes some time. My data entry person does not like to
fill in that sheet form, so I built him a UserForm that makes the
sheet form for data entry obsolete. It is shill used as a printing
template to print hard copies of the customers info.

It is working for the most part now, but I think I'll try to use the
Toolbox ComboBox just to see if I can. <G>

Thanks also to Gord and JLGWhiz for adding to this discussion

-Minitman