Excel Script for Drop Down Menu

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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