Excel Script for Drop Down Menu
 

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
 

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
 

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.

"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