Drop-down list code - how does it work?

[elanus]

I have a spreadsheet that allows me to make selections from a drop-dow

list of values in column A. I don't remember where I found the code
and
I have modified it a little and it now does what I want, offering all
values from Row 1 to the end of the list.

I can now right-click any cell in any other column, select 'Pick From
List' (at which point no items are displayed in the resulting drop-dow

list), then click the drop-down arrow which now presents itself and th

entire list from column A is made available for selection. This is
perfect for my needs.

My question is: 'How does it work?'
__________________________

Option Explicit
Dim strRange As String

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel A

Boolean)
Dim cBar As CommandBarPopup
If Target.Row = 1 Then End
If Target.Cells.Count > 1 Then Exit Sub

'Uncomment below to supress pop-up
'Cancel = True

'Parse a range address containing all cells above active cell
' strRange = Target.EntireColumn.Cells(1, 1).Address & _
' ":" & Target.Offset(-1, 0).Address

strRange = "$a$1:$a$4444"


'Add some validation using the "List" option _
and our variable strRange as the range for the list.
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & strRange
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With

'Move normal Pop-up out the way
On Error Resume Next
If Not Cancel Then _
Application.CommandBars("Cell").ShowPopup x:=Target.Offset(0, 5).Left

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Remove all validation in the Column
If strRange <> vbNullString Then Range
(strRange).EntireColumn.Validation.Delete
'strRange = vbNullString
strRange = "$a$1:$a$4444"
End Su

 

[Rob van Gelder]

It gives the target cell (the one which you right-clicked) a data validation
with an incell dropdown.

It looks as though it's designed to work for Column A because of the way
SelectionChange clears validation later.

Try to record a macro and play with the Data Validation features. Then
inspect the code which has been generated by macro recorder. You'll see the
code is fairly similar.