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
Drop down event?