Thanks for the thanks, but I am not sure I have helped you that much.
Anyway, the main problem is that when a user is editing a cell, macros do
not run for any practical purpose. You could use combobox from the controls
toolbox toolbar which supports this directly.
You could have a combobox pop up so to speak by using the selection_change
event.
Possibly something like this in the sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oOLE As OLEObject
For Each OLEObject In Me.OLEObjects
If OLEObject.TopLeftCell.Column = 3 Then
OLEObject.Delete
End If
Next
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
Set oOLE = Me.OLEObjects.Add( _
ClassType:="Forms.Combobox.1", _
Left:=Target.Left, Top:=Target.Top, _
Width:=Target.Width, _
Height:=Target.Height)
oOLE.ListFillRange = "sheet1!A1:A10"
oOLE.Object.MatchEntry = fmMatchEntryComplete
oOLE.LinkedCell = Target.Address
oOLE.Activate
End If
End Sub
Change Sheet1!A1:A10 to point to the source of your data. You data souce
should be sorted.
Change Target.Column = 3 to the column where you want the activity.