I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?

Try put this in ur sheet-tab-code-window
check in H18:H24 and make drop-list in H2

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" skrev:

I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?

I'm not familiar with using the Visual Basic editor. Please explain how to
insert the code into the worksheet.

"excelent" wrote:

Try put this in ur sheet-tab-code-window
check in H18:H24 and make drop-list in H2

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" skrev:

I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?

ok i dont no the UK-version but ill try anyway

Rightclick on sheet-tab
select "Show program code" - or som like that
paste code in that window

drop-down list will be in H2

"Matt" skrev:

I'm not familiar with using the Visual Basic editor. Please explain how to
insert the code into the worksheet.

"excelent" wrote:

Try put this in ur sheet-tab-code-window
check in H18:H24 and make drop-list in H2

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" skrev:

I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?

i am US version, so that worked. i changed the "" to "0" and it did the
trick. i have about 7 of these running on one 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? Thanks for your help.

Matt


"excelent" wrote:

ok i dont no the UK-version but ill try anyway

Rightclick on sheet-tab
select "Show program code" - or som like that
paste code in that window

drop-down list will be in H2

"Matt" skrev:

I'm not familiar with using the Visual Basic editor. Please explain how to
insert the code into the worksheet.

"excelent" wrote:

Try put this in ur sheet-tab-code-window
check in H18:H24 and make drop-list in H2

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" skrev:

I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?

one more question for you. when i change parameters and the choices change i
need to reset the default selection. how do i do that?

"excelent" wrote:

ok i dont no the UK-version but ill try anyway

Rightclick on sheet-tab
select "Show program code" - or som like that
paste code in that window

drop-down list will be in H2

"Matt" skrev:

I'm not familiar with using the Visual Basic editor. Please explain how to
insert the code into the worksheet.

"excelent" wrote:

Try put this in ur sheet-tab-code-window
check in H18:H24 and make drop-list in H2

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" skrev:

I am trying to construct a drop down list with a variable number of items to
select. I have data in a list (=$H$18:$H$24). Each field is turned on or
off based on exterior parameters. Currently, if my drop down list has half
the possible choices the rest are blank. How do I remove the blank spaces?
Can I do this in the validation screen or do I need to change the list I am
pulling my options from?