ExcelBanter - How to run code based on a dropdown form changing?

There are two dropdown looking objects.

One is a combobox from the control toolbox toolbar. The other is a dropdown
from the Forms toolbar.

They behave differently.

If you used a combobox from the control toolbox toolbar, you can set up the
linkedcell and listfillrange (where to store the value in the combobox and where
to get the list) by rightclicking on the combobox and selecting properties.
(You'll have to be in design mode to do this.)

Then double click on that combobox and you'll see where the code goes:

Option Explicit
Private Sub ComboBox1_Change()
MsgBox Me.ComboBox1.Value
End Sub

If you used a dropdown from the forms toolbar, you can rightclick on it and
select format control. From there, you can specify the Cell Link and the Input
Range (same kind of things as linkedcell and listfillrange above).

But you put the code in a general module (very different from the control
toolbox toolbar combobox!).

Here's some sample code:

Option Explicit
Sub ddChange()
Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
If .ListIndex 0 Then
MsgBox .List(.ListIndex)
End If
End With
End Sub

And you rightclick on the DropDown and select assign macro to, er, assign the
macro.

If you use linked cells (not a requirement--but usually helpful), you'll notice
that the combobox linkedcell shows the value of the combobox.

The dropdown's cell link shows an index into that list.

if A1:A10 held the list, and B1 was the linked cell, then this (in C1???) would
show the value:

if(b1=0,"",index(a1:a10,b1))

====
I find the dropdowns easier to work with (usually). And I can use the same
macro for all my dropdowns. I can't do that as easily with comboboxes.

PropKid wrote:

That makes sense. The only problem is that I can't figure out how to
edit the dropdown box. You can't pull it up in the forms editor. I was
just pasted into the spreadsheet so I'm not even sure what the name of
the object is.

Your thoughts??

--
PropKid
------------------------------------------------------------------------
PropKid's Profile: http://www.excelforum.com/member.php...o&userid=10134
View this thread: http://www.excelforum.com/showthread...hreadid=271369

--

Dave Peterson