Update cell immediately upon selection from drop down

[Guest]

I have "Drop Down 1" on a spreadsheet (NOT an activeX) and when the user
makes a choice from the drop down I want the text from that choice to
immediately populate column "A" on the row in which the cell pointer
currently resides.

The problem is, if the user's choice already appears, then it doesn't work,
even if the user opens the drop down and closes it again (by making the same
choice) because the drop down hasn't changed. The user has to make some
other choice, then return to their original choice to get it to work.

My function is tied to the control itself. How can I get this to function
correctly? My function follows:

Public Function ControlSSDropDownChange()
'POPULATE CELL WITH SHEET CONTROL CONTENTS
Dim ctrlDD1 As ControlFormat
Set ctrlDD1 = ThisWorkbook.ActiveSheet.Shapes(pcstrDropDown1).ControlFormat
Cells(ActiveCell.Row, 1) = ctrlDD1.List(ctrlDD1.ListIndex)
End Function

Thanks much in advance...

 

[Guest]

Quartz,
Try the KeyUp procedure...
ControlSSDropDown_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift
As Integer)

HTH,
Gary Brown

 

[Guest]

This works for me:

I format the dropdown as follows:
Input range: MyList (a named range in the wbk)
Link cell: LinkCell (another named range -single cell)

Option Explicit

Sub DropDown1_Change()

'This sub will put the selected choice in ColA of the selected row
'then clear the drop down box

Cells(ActiveCell.Row, 1) = Application.Index(Range("MyList"),
Range("LinkCell"))
Range("LinkCell") = ""
End Sub

 

[Dave Peterson]

You have one more suggestion at your other thread.