Code to an in cell drop down list

[Guest]

I would like to assign code to an in cell drop down list..... so that once an
item is chosen it would run the code. Is this possible? I know you can
attach code to a control list box, but I am having trouble creating a
dependant list like I can with the in cell drop down.

Thanks for any help.

 

[Otto Moehrbach]

You can use a Worksheet_Change event macro like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> A1 Then Exit Sub
If Target = "" Then Exit Sub
Call YourMacro
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End Sub
This macro will run your macro whenever the contents of A1 are changed to
anything other than blank. This macro must be placed in the sheet module of
your sheet. To do this, right-click on the tab of your sheet, select View
Code, and paste this macro into that module. HTH Otto

 

[Guest]

I cannot get this code to work. I have tried writing the instructions in a
separate macro, and also replacing the "yourmacro" with the instructions
directly and neither runs the code upon changing A1 or any other cell I try.

What am I doing wrong?????

 

[Dave Peterson]

Just to add to Otto's response.

There's a bug in xl97 that stops the worksheet_change event from firing under
certain conditions:

From Debra Dalgleish's site:
http://contextures.com/xlDataVal08.html#Change

In Excel 97, selecting an item from a Data Validation dropdown list does not
trigger a Change event, unless the list items have been typed in the Data
Validation dialog box.

She also has some workarounds for this bug.

 

[Dave Peterson]

This code goes behind the worksheet that has the dropdown (you built the
dropdown via data|validation, didn't you???).

Right click on the worksheet tab, select view code and paste Otto's code there.

(change YourMacro to the correct name, too.)