I've come to the same dead end of wanting to run a macro with th
in-cell drop down validation. What I ended up doing was inserting
combo box in the sheet (from the forms toolbar). You can specify th
range of cells to use in its list of values, then, assign a cell for i
to link to. This link cell just returns the position within the list o
the value you selected.
For example, if your list was {Apples, Oranges, Grapes, Lemons}, an
you selected lemons, the linked cell would have '4' in it (the 4t
value). Although returning the values has some advantages, returnin
the position is good for vlookups.
After you have your combo box, you can assign code to it that run
every time the value in it is changed. Going back to the previou
example, If you select Lemons, the linked cell (best if you assign
name to it) would have a value of 4, and you can write code that run
specific lines (or call other macros) based on the value of the linke
cell.

To do what you want, I think it may be easiest to use the 'select case
function (conceptually the same as SWITCH in c++ if you're familiar)
Here's an example.

Sub DropDown1_Change()

Select Case Range("*THE NAME OF THE LINKED CELL*").Value

Case 1 '<<if you choose the first value in the dropdown
Windows("*WORKBOOK B.xls*").Activate
Range("G4:G800").Copy
Windows("*WORKBOOK A.xls*").Activate
Range("G4").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Case 2
'Insert code, or call another function
Case 3
'Insert code, or call another function
Case 4
'Insert code, or call another function
End Select

End Sub

Hope this helps

-Gitcyphe

--
Message posted from http://www.ExcelForum.com