Excel Script for Drop Down Menu

M

Matt

I don't think either of those things will work because each drop down list
pulls data from a different place. i only have 1 year of C++ as programming
experience and i feel like the solution is possible, but i don't know enough
VB to figure it out. Please help.

the stuff below is from yesterday.
 
J

JE McGimpsey

It's hard to help when you say "I don't think it will work", but don't
say why...

Pulling data from "a different place" is certainly possible when the
_SelectionChange event target range is handled serially.
 
M

Matt

sorry, i had copied the answer i got yesterday underneath, or so i thought.

the two suggestions below were given. both use the same set of data to
create two different lists. i need two sets of data to create two different
lists. again, i kind of need it spelled out for me. thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") <> "" Then List = List & "," & Cells(t, "H")
Next
With Range("H2").Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
With Range("H2").Validation '<=== change H4 to the real cell
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With

'etc.

End Sub

or you could a utility proc


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") <> "" Then List = List & "," & Cells(t, "H")
Next

Call SetupDV(Me.Range("H2")
Call SetupDV(Me.Range("H4")
'etc.

End Sub

Private Sub SetupDV(rng As Range)
With rng.Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub
 
J

JE McGimpsey

I'm still not sure what you're trying to do - since you started a new
thread, I don't see your original problem statement.

If you're trying to put two individual lists in two cells, respectively,
one way is:

This assumes that list 1 (from H18:H24) should go in H2, and list 2
(J18:J24) should go in J2. Change to suit.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim vArray As Variant
Dim i As Long
Dim sList As String

If Not Intersect(Target.Cells, Range("H2, J2")) Is Nothing Then
'List 1
sList = vbNullString
vArray = Range("H18:H24").Value
For i = LBound(vArray, 1) To UBound(vArray, 1)
If vArray(i, 1) <> vbNullString Then _
sList = sList & "," & vArray(i, 1)
Next i
With Range("H2").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=sList
.InCellDropdown = True
End With

'List 2
sList = vbNullString
vArray = Range("J18:J24").Value
For i = LBound(vArray, 1) To UBound(vArray, 1)
If vArray(i, 1) <> vbNullString Then _
sList = sList & "," & vArray(i, 1)
Next i
With Range("J2").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=sList
.InCellDropdown = True
End With
End If
End Sub
 
Top