Cell value change on drop-down list item change

[vamshivemula]

Hi,

I need a cell value (assuming B1) to be changed as soon as you select an item from other Excel drop-down list cell (assuming A1) and move AWAY from that cell (like "Lostfocus" event in VB). Both A1 and A2 are in Sheet1 of Workbook. This should happen without button press, worksheet change/activate, etc. For example, I have one column that is being pulled to the drop-down list from different sheet (assuming Sheet2) and its related info in the column parallel to it in Sheet2. I want this function to be called when the drop-down list loses focus. I currently did it in different way, i.e., I have written VBA code on "Worksheet_SelectionChange" when it reaches to the next cell (taking ActiveCell.Address - assuming cell A2 gets selected/activated), but a user has to go to that particular cell (A2) to run this VBA function, otherwise it never triggers. I want this function to be triggered when A1 (drop-down list cell) get deselected or loses focus. Your help is greatly appreciated.

Regards,

Vamshi

 

[vamshivemula]

Thanks Rory...

Your tip worked and as per your tip, I modified your example code per my requirements and the code goes like this:

Range(“I9”) is drop-down in Sheet1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For name cell >>>>> “=PullName(I9)”<o></o>
For E-mail cell >>>>> “=PullEmail(I9)”<o></o>
<o></o>
Function PullName(myName) As String<o></o>
myRow = 4<o></o>
myCol = 3<o></o>
<o></o>
Do Until Sheet7.Cells(myRow, myCol).Value = ""<o></o>
If Sheet7.Cells(myRow, myCol) = myName Then<o></o>
myName = Sheet7.Cells(myRow, myCol - 1)<o></o>
Exit Do<o></o>
Else<o></o>
myRow = myRow + 1<o></o>
End If<o></o>
Loop<o></o>
PullName = myName<o></o>
End Function<o></o>
<o></o>
Function PullEmail(myEmail) As String<o></o>
myRow = 4<o></o>
myCol = 3<o></o>
<o></o>
Do Until Sheet7.Cells(myRow, myCol).Value = ""<o></o>
If Sheet7.Cells(myRow, myCol) = myEmail Then<o></o>
myEmail = Sheet7.Cells(myRow, myCol + 1)<o></o>
Exit Do<o></o>
Else<o></o>
myRow = myRow + 1<o></o>
End If<o></o>
Loop<o></o>
PullEmail = myEmail<o></o>
End Function


Thanks a lot again Rory... Your Rock!!
<!-- / message -->

 

[Smitty]

Welcome to the Board!

It'll help if you stick to your Original Post.

That way everyone can follow along.