Choosing a drop-down menu selection returns value to another cell

[Mica]

Version: Excel 2003

I have a worksheet that is referencing the value of the active cell. The
point is to provide users with dynamic details of the project they currently
have selected (active cell). I have the following worksheet code to return
the value of the active cell to A1 and the row# of the active cell to A2.
Lookup formulas search a database for detail matches on the value of the
active cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1") = Target.Value
Range("A2") = Target.Row
End Sub

The range A20:A500 has data validation to allow a list located in another
worksheet. The code to return the row of the active cell works fine, but the
value of the active cell is not returned to A1 when I select from the
drop-down menu. It is only returned once I leave that active cell and come
back to it. In other words, it is not dynamically returning the value of the
active cell when the value of the active cell changes from blank to a
selection from the drop-down list. It is only dynamic when toggling the
active cell between non-blank cells.

Ideally, I would like the code to return the value of the active cell to A1
as soon as the drop-down selection is made, before leaving the active cell.
I'm not sure if this is considered an event.

The worksheet event is Selection_Change. Is returning an offset value of
the row # an option?

Thank you,
Mica

 

[Barb Reinhardt]

What do you have in your worksheet_Change event at this point? Anything?

 

[Mica]

Hi Barb,

So far I only have the code to return the active cell value to A1 and the
active row # to A2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1") = Target.Value
Range("A2") = Target.Row
End Sub

When I select from the drop-down list, it doesn't populate A1 immediately.
A1 remains blank until I leave the active cell and come back to it, then it
works.

Thanks for any suggestions.

- Mica