Naming a List Fill range for a drop down box

Posted by Jennifer on August 14, 2001 1:01 PM

Hello!

I am currently developing an application in Excel using VBA. I have a dropdown box that I need help defining the list fill range.

Currently the only sytax that seems to work is .ListFillRange = "sheet1!$A$1:$A$62". Here is the problem...The range is variable. One time it may be 52 the next it may be 78.

How can I code the list fill range to accept a variable number. I have added a count to a cell in the same sheet. What I would like to do is call the value in that cell as the end cell for the drop down list.

Example: Say the values I want in the dropdown box is A1 thru A50, cell B5 has the value of the counter which is 50.

How can I code .ListFillRange = "sheet1!$A$1:$A$62" where 62 is the value of cell B5?

Thanks,

Jennifer

Posted by Aladin Akyurek on August 14, 2001 1:13 PM

Jennifer,

Activate A1.
Activate the option Insert|Name|Define.
Enter (e.g.,) SourceList for Names in Workbook.
Enter the following formula for Refers To:

=OFFSET(x!$A$1,0,0,COUNTA($A:$A),1)

where x is the name of the worksheet.

Use this as the value of ListFillRange (although I'm not sure whether this assignment is possible or not in VBA).

Aladin

Posted by faster on August 14, 2001 1:35 PM

This may work for you, right click the sheet you
are working in and paste this code into it's module.

Assumes your drop down box is named "drp1", change
as needed.

Private Sub Worksheet_Change(ByVal Target As Range)

Static MyEnd

If MyEnd = Empty Then
MyEnd = Range("B5")
ElseIf MyEnd = Range("B5") Then
Exit Sub
End If

Dim MySelection
MySelection = ActiveCell.Address

ActiveSheet.Shapes("drp1").Select
Selection.ListFillRange = "$A$1:$A$" & Range("b5")

'reset selection
Selection.ListIndex = 1

Range(MySelection).Select
MyEnd = Range("B5")

End Sub