Hi

I have been achieving this via a right-click menu as per
below. "ColumnData.TradeDataItems" is simply an array of
values that contain all the values:

As well as creating the menu you have to firstly call this
function via the BeforeRightClick even:


Private Sub Worksheet_BeforeRightClick(ByVal Target As

'If statement - is this a good place to show the menu

Set rngRightClickTarget = Target
BuildValueChooser ColumnData

Else ' Make sure you delete any old menus
Application.CommandBars("Cell").Reset
Exit Sub
End If


End Sub

Which calls the below

Public Sub BuildValueChooser(ColumnData As clsColumnData)
Dim i As Long

Dim Mnu As Object

On Error Resume Next


Application.CommandBars("Cell").Reset



Set Mnu = Application.CommandBars("Cell").Controls.Add
(msoControlPopup, , , 1, True)

With Mnu
.Caption = "QuickSelect"
.Tag = "QuickSelect"
' .DescriptionText = "QuickSelect"
End With


With Mnu.Controls


For i = LBound(ColumnData.TradeDataItems) To UBound
(ColumnData.TradeDataItems)
With .Add(msoControlButton, , , i, True)
.Caption = ColumnData.TradeDataItems(i)
.Tag = ColumnData.TradeDataItems(i)
.DescriptionText = ColumnData.TradeDataItems
(i)
.OnAction = "'CustomSetData """ &
ColumnData.TradeDataItems(i) & """'"


If i = LBound(ColumnData.TradeDataItems)
Then .BeginGroup = True
End With


Next i


End With

End Sub



Then you need a handler for the "onaction"


Public Sub CustomSetData(DataToSet As Variant)

rngRightClickTarget.Value = DataToSet

End Sub