Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to run code based on a dropdown form changing?
That makes sense. The only problem is that I can't figure out how t edit the dropdown box. You can't pull it up in the forms editor. I wa just pasted into the spreadsheet so I'm not even sure what the name o the object is. Your thoughts? -- PropKi ----------------------------------------------------------------------- PropKid's Profile: http://www.excelforum.com/member.php...fo&userid=1013 View this thread: http://www.excelforum.com/showthread.php?threadid=27136 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown list width changing code | Excel Worksheet Functions | |||
offer dropdown options based on another dropdown | Excel Discussion (Misc queries) | |||
Use dropdown list from Form | Excel Discussion (Misc queries) | |||
How to run code based on a dropdown form changing? | Excel Programming | |||
Executing code based on changes made to a form ? | Excel Programming |