placing a pull-down menu at a cell location automatically

  • Thread starter Thread starter Ken Nysse
  • Start date Start date
K

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
 
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
 
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".
 
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
 
Back
Top