Excel Script for Drop Down Menu
 

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.

"Bob Phillips" wrote:

You cannot have more than one SelectionChange event in that sheet.

You need to process ad serium

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Matt" wrote in message
...
i some kind person gave me the code below to insert into my spreadsheet to
remove the white space from my drop down menus. the issue is that i have
15
of these on the same page. can i just copy and
paste the same codes below the first (changing names and fields of
course),
or do i need to make a new module? Please advise.

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
End Sub


Matt

Excel Script for Drop Down Menu
 

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


"JE McGimpsey" wrote:

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.


In article ,
Matt wrote:

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.

Excel Script for Drop Down Menu
 

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



In article ,
Matt wrote:

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


"JE McGimpsey" wrote:

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.


In article ,
Matt wrote:

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.