placing a pull-down menu at a cell location automatically

[Ken Nysse]

Hi there,

I'm wanting to click on any cell in Excel and wanting a
pull down menu to be positioned over that cell location,
when I double click on that cell.

The pull down menu has a list of values (text values), and
once I select a text value from the pull down list, that
value automatically appears in the cell, and the pull down
menu then dissapears.

How do I code this in visual basic?

Cheers KEN

 

[J.E. McGimpsey]

one way:

Put this in your worksheet code module:

Option Explicit
Public gOldActiveCell As Range

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With ActiveCell
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="txt1,txt2,txt3,txt4"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
Cancel = True
End Sub

Private Sub Worksheet_SelectionChange( _
ByVal Target As Excel.Range)
If Not gOldActiveCell Is Nothing Then _
gOldActiveCell.Validation.Delete
Set gOldActiveCell = ActiveCell
End Sub

 

[keepitcool]

another way:

use Data/Validation.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[J.E. McGimpsey]

Note that unless you apply data validation to *every* cell in the
sheet, this can't meet the OP's requirement that the dropdown should
appear when he clicks "on any cell", and doesn't meet at all the
requirement that it appear "when I double click on that cell".

 

[strataguru]

Instead of having a listbox drop down - is it possible to have a check
box drop down so the user can choose multiple values (seperated by a
comma)?

Thanks,
Robin