Drop down event? 
Author Message
 Drop down event?

I have an Excel2000 workbook with dropdowns in it to choose various
parameter settings. I *was* using the Control cell link to set the index of
another cell and then read the value. I now need the dropdown to trigger a
macro. I tried the following:

1. Used worksheet_change to recognize the changed linked cell. This does not
recognize the change, it must consider this a recalc and not trigger the
change event.

2. Assigned the macro to the dropdown to run. This runs the macro and
changes the dropdown display, but does NOT change the linked cell.

So, is there a better way to get BOTH the changed dropdown value AND run the
macro without having to recognize the name of the dropdown that did this
(there are 10 or so of these).

I think I saw an event to do with dropdowns, but can't find it. Is there a
value property of the dropdown itself? What is the syntax. The help on
dropdowns (and shapes) is pitiful.

Thanks in advance for the help.

Terry



Fri, 25 Nov 2005 01:44:37 GMT  
 Drop down event?
Terry,

There is probably a better way to do this, but here's a workaround.
If your linked cell is say A1, you could place a formula in some
obscure part of the sheet that would simply be =A1.
What this does is force the sheet to recalculate whenever the linked
cell is changed. In that way, you could use the Worksheet_Calculate
event to call your macro after the linked cell is updated.

Again, there's probably a better way and if so, I'm sure that someone
will chime in soon with it.

John

Quote:

> I have an Excel2000 workbook with dropdowns in it to choose various
> parameter settings. I *was* using the Control cell link to set the index of
> another cell and then read the value. I now need the dropdown to trigger a
> macro. I tried the following:

> 1. Used worksheet_change to recognize the changed linked cell. This does not
> recognize the change, it must consider this a recalc and not trigger the
> change event.

> 2. Assigned the macro to the dropdown to run. This runs the macro and
> changes the dropdown display, but does NOT change the linked cell.

> So, is there a better way to get BOTH the changed dropdown value AND run the
> macro without having to recognize the name of the dropdown that did this
> (there are 10 or so of these).

> I think I saw an event to do with dropdowns, but can't find it. Is there a
> value property of the dropdown itself? What is the syntax. The help on
> dropdowns (and shapes) is pitiful.

> Thanks in advance for the help.

> Terry



Fri, 25 Nov 2005 03:02:28 GMT  
 Drop down event?
I tried that as well. This creates an endless loop.
I do have calculation set to manual and screenupdating false and
enableevents false in the macro since this macros changes about 1000 cells,
but when they get turned back on at the end of the macro it triggers a
recalc and gets called again.

So, if there were an event trigger by virtue of selecting the dropdown that
would be ideal. Thanks for the help.

Terry


Quote:

> Terry,

> There is probably a better way to do this, but here's a workaround.
> If your linked cell is say A1, you could place a formula in some
> obscure part of the sheet that would simply be =A1.
> What this does is force the sheet to recalculate whenever the linked
> cell is changed. In that way, you could use the Worksheet_Calculate
> event to call your macro after the linked cell is updated.

> Again, there's probably a better way and if so, I'm sure that someone
> will chime in soon with it.

> John


>> I have an Excel2000 workbook with dropdowns in it to choose various
>> parameter settings. I *was* using the Control cell link to set the index of
>> another cell and then read the value. I now need the dropdown to trigger a
>> macro. I tried the following:

>> 1. Used worksheet_change to recognize the changed linked cell. This does not
>> recognize the change, it must consider this a recalc and not trigger the
>> change event.

>> 2. Assigned the macro to the dropdown to run. This runs the macro and
>> changes the dropdown display, but does NOT change the linked cell.

>> So, is there a better way to get BOTH the changed dropdown value AND run the
>> macro without having to recognize the name of the dropdown that did this
>> (there are 10 or so of these).

>> I think I saw an event to do with dropdowns, but can't find it. Is there a
>> value property of the dropdown itself? What is the syntax. The help on
>> dropdowns (and shapes) is pitiful.

>> Thanks in advance for the help.

>> Terry



Fri, 25 Nov 2005 15:18:30 GMT  
 Drop down event?
Terry,

okay, back to the basics....
By DropDown, you do mean ComboBox???
Is it on a worksheet or a UserForm?
Did you use one from the "Forms" toolbar or the
"Control Toolbox"??

John

Quote:

> I tried that as well. This creates an endless loop.
> I do have calculation set to manual and screenupdating false and
> enableevents false in the macro since this macros changes about 1000 cells,
> but when they get turned back on at the end of the macro it triggers a
> recalc and gets called again.

> So, if there were an event trigger by virtue of selecting the dropdown that
> would be ideal. Thanks for the help.

> Terry



> > Terry,

> > There is probably a better way to do this, but here's a workaround.
> > If your linked cell is say A1, you could place a formula in some
> > obscure part of the sheet that would simply be =A1.
> > What this does is force the sheet to recalculate whenever the linked
> > cell is changed. In that way, you could use the Worksheet_Calculate
> > event to call your macro after the linked cell is updated.

> > Again, there's probably a better way and if so, I'm sure that someone
> > will chime in soon with it.

> > John


> >> I have an Excel2000 workbook with dropdowns in it to choose various
> >> parameter settings. I *was* using the Control cell link to set the index of
> >> another cell and then read the value. I now need the dropdown to trigger a
> >> macro. I tried the following:

> >> 1. Used worksheet_change to recognize the changed linked cell. This does not
> >> recognize the change, it must consider this a recalc and not trigger the
> >> change event.

> >> 2. Assigned the macro to the dropdown to run. This runs the macro and
> >> changes the dropdown display, but does NOT change the linked cell.

> >> So, is there a better way to get BOTH the changed dropdown value AND run the
> >> macro without having to recognize the name of the dropdown that did this
> >> (there are 10 or so of these).

> >> I think I saw an event to do with dropdowns, but can't find it. Is there a
> >> value property of the dropdown itself? What is the syntax. The help on
> >> dropdowns (and shapes) is pitiful.

> >> Thanks in advance for the help.

> >> Terry



Fri, 25 Nov 2005 17:16:00 GMT  
 Drop down event?
First, I must apologize. The proposed solution DOES work.
I had the index in a cell which doesn't trigger the Calculation event, but I
did not use it to set another cell as suggested. Doing this DOES cause the
Calculation event to trigger. So my macro gets called from the Drop Down and
referencing the indexed cell causes Calculation event to be called.

It gets named "Drop Down 7" or similar when you put it in. I'm upgrading
another's workbook and have just been copying the existing ones so I don't
know what it's actually called.

Anyway, it appears my problem is solved, although if there's a more elegant
solution...

Thanks. Terry


Quote:

> Terry,

> okay, back to the basics....
> By DropDown, you do mean ComboBox???
> Is it on a worksheet or a UserForm?
> Did you use one from the "Forms" toolbar or the
> "Control Toolbox"??

> John


>> I tried that as well. This creates an endless loop.
>> I do have calculation set to manual and screenupdating false and
>> enableevents false in the macro since this macros changes about 1000 cells,
>> but when they get turned back on at the end of the macro it triggers a
>> recalc and gets called again.

>> So, if there were an event trigger by virtue of selecting the dropdown that
>> would be ideal. Thanks for the help.

>> Terry



>>> Terry,

>>> There is probably a better way to do this, but here's a workaround.
>>> If your linked cell is say A1, you could place a formula in some
>>> obscure part of the sheet that would simply be =A1.
>>> What this does is force the sheet to recalculate whenever the linked
>>> cell is changed. In that way, you could use the Worksheet_Calculate
>>> event to call your macro after the linked cell is updated.

>>> Again, there's probably a better way and if so, I'm sure that someone
>>> will chime in soon with it.

>>> John


>>>> I have an Excel2000 workbook with dropdowns in it to choose various
>>>> parameter settings. I *was* using the Control cell link to set the index of
>>>> another cell and then read the value. I now need the dropdown to trigger a
>>>> macro. I tried the following:

>>>> 1. Used worksheet_change to recognize the changed linked cell. This does
>>>> not
>>>> recognize the change, it must consider this a recalc and not trigger the
>>>> change event.

>>>> 2. Assigned the macro to the dropdown to run. This runs the macro and
>>>> changes the dropdown display, but does NOT change the linked cell.

>>>> So, is there a better way to get BOTH the changed dropdown value AND run
>>>> the
>>>> macro without having to recognize the name of the dropdown that did this
>>>> (there are 10 or so of these).

>>>> I think I saw an event to do with dropdowns, but can't find it. Is there a
>>>> value property of the dropdown itself? What is the syntax. The help on
>>>> dropdowns (and shapes) is pitiful.

>>>> Thanks in advance for the help.

>>>> Terry



Fri, 25 Nov 2005 18:10:52 GMT  
 Drop down event?
Terry,

Quote:
>although if there's a more elegant solution

I do know that the guru's here eventually browse through all the
threads and if I steered you wrong or there was a more elegant
solution, I'm sure they would have chimed in by now.
And, as an aside, I appreciate when they do as I often learn
how to do things better and/or more gracefully.

If you do want to see if there is a more elegant way, might I
suggest that you post the solution that I gave you in a new
thread asking if there's a better way to do it.

On the other hand, since..........

Quote:
>The proposed solution DOES work.

being elegant isn't always a necessity.

John

Quote:

> First, I must apologize. The proposed solution DOES work.
> I had the index in a cell which doesn't trigger the Calculation event, but I
> did not use it to set another cell as suggested. Doing this DOES cause the
> Calculation event to trigger. So my macro gets called from the Drop Down and
> referencing the indexed cell causes Calculation event to be called.

> It gets named "Drop Down 7" or similar when you put it in. I'm upgrading
> another's workbook and have just been copying the existing ones so I don't
> know what it's actually called.

> Anyway, it appears my problem is solved, although if there's a more elegant
> solution...

> Thanks. Terry



> > Terry,

> > okay, back to the basics....
> > By DropDown, you do mean ComboBox???
> > Is it on a worksheet or a UserForm?
> > Did you use one from the "Forms" toolbar or the
> > "Control Toolbox"??

> > John


> >> I tried that as well. This creates an endless loop.
> >> I do have calculation set to manual and screenupdating false and
> >> enableevents false in the macro since this macros changes about 1000 cells,
> >> but when they get turned back on at the end of the macro it triggers a
> >> recalc and gets called again.

> >> So, if there were an event trigger by virtue of selecting the dropdown that
> >> would be ideal. Thanks for the help.

> >> Terry



> >>> Terry,

> >>> There is probably a better way to do this, but here's a workaround.
> >>> If your linked cell is say A1, you could place a formula in some
> >>> obscure part of the sheet that would simply be =A1.
> >>> What this does is force the sheet to recalculate whenever the linked
> >>> cell is changed. In that way, you could use the Worksheet_Calculate
> >>> event to call your macro after the linked cell is updated.

> >>> Again, there's probably a better way and if so, I'm sure that someone
> >>> will chime in soon with it.

> >>> John


> >>>> I have an Excel2000 workbook with dropdowns in it to choose various
> >>>> parameter settings. I *was* using the Control cell link to set the index of
> >>>> another cell and then read the value. I now need the dropdown to trigger a
> >>>> macro. I tried the following:

> >>>> 1. Used worksheet_change to recognize the changed linked cell. This does
> >>>> not
> >>>> recognize the change, it must consider this a recalc and not trigger the
> >>>> change event.

> >>>> 2. Assigned the macro to the dropdown to run. This runs the macro and
> >>>> changes the dropdown display, but does NOT change the linked cell.

> >>>> So, is there a better way to get BOTH the changed dropdown value AND run
> >>>> the
> >>>> macro without having to recognize the name of the dropdown that did this
> >>>> (there are 10 or so of these).

> >>>> I think I saw an event to do with dropdowns, but can't find it. Is there a
> >>>> value property of the dropdown itself? What is the syntax. The help on
> >>>> dropdowns (and shapes) is pitiful.

> >>>> Thanks in advance for the help.

> >>>> Terry



Fri, 25 Nov 2005 18:53:13 GMT  
 Drop down event?
I use xl2002, but don't remember any change for this.

I dropped a combobox from the Forms toolbar onto a worksheet.  I rightclicked
and used format control to set the cell link and input range.

I assigned it a macro.

Option Explicit
Sub testme()

    Dim myDropDown As DropDown

    Set myDropDown = ActiveSheet.DropDowns(Application.Caller)

    MsgBox myDropDown.Value & vbLf & _
        myDropDown.List(myDropDown.Value)

End Sub

(Located in a General module.)

The macro fired when I changed the value.  The linked cell changed, too.  (Are
you sure yours didn't change?)

And by using the application.caller stuff, I can assign this to as many
dropdowns as I want.

====
And you might have been thinking about comboboxes from the Control Toolbox
toolbar.  They have a bunch of events associated with them.

Quote:

> I have an Excel2000 workbook with dropdowns in it to choose various
> parameter settings. I *was* using the Control cell link to set the index of
> another cell and then read the value. I now need the dropdown to trigger a
> macro. I tried the following:

> 1. Used worksheet_change to recognize the changed linked cell. This does not
> recognize the change, it must consider this a recalc and not trigger the
> change event.

> 2. Assigned the macro to the dropdown to run. This runs the macro and
> changes the dropdown display, but does NOT change the linked cell.

> So, is there a better way to get BOTH the changed dropdown value AND run the
> macro without having to recognize the name of the dropdown that did this
> (there are 10 or so of these).

> I think I saw an event to do with dropdowns, but can't find it. Is there a
> value property of the dropdown itself? What is the syntax. The help on
> dropdowns (and shapes) is pitiful.

> Thanks in advance for the help.

> Terry

--

Dave Peterson



Sat, 26 Nov 2005 01:52:19 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. drop down list w/out drop down box

2. XL95/XL97 - Using a drop-down selection to load a fill range in another drop-down

3. Drop Down Box depending upon another Drop Down Box

4. restrict drop down menu based on previous drop down menu

5. Drop Down within a Drop Down

6. unbound drop-down list box to limit the values in another drop-down list box

7. Drop down menus keep popping up &dropping down

8. Change-Event and Drop Down Lists

9. Prevent combo box from automatically dropping down after the NotInList event

10. Data Validation Drop-down and Events (Excel 97)


 
Powered by phpBB® Forum Software © phpBB Group