Drop Downs Menus

[GBH99]

I am having difficulty making a drop down list for an excel cell, I don't
want it to filter, I need it to select pre-determined values. Can anyone help

 

[tjtjjtjt]

Here are a few ways:
Use the Combo Box Control from the ControlToolbox.
Use the Combobox control from the Forms toolbar

It should be noted that these two Combo Boxes are NOT the same, and you may
want to check the Help Files on them. Also, they don't go directly into cells.
You could also:

Check out Data | Validation and set the Allow to List for the cell or cells
you want a list to appear in.

tj

 

[Frank Kabel]

Hi
see:
http://www.contextures.com/xlDataVal01.html

 

[Max]

As mentioned by tjtjjtjt, think
data validation (DV) could be what you're after ..

Try Debra Dalgleish's nice coverage on DV at her:
http://www.contextures.com/xlDataVal01.html

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

I am having difficulty making a drop down list for an excel cell, I don't
want it to filter, I need it to select pre-determined values. Can anyone

help

 

[Ben McBen]

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