FORM Drop Down issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 26 Sep 2007 18:19 GMT

I am using the following code to populate a drop down on a FORM in Excel.

Public Sub PopulatePositions()
Dim AllCells As Range, Cell As Range
Dim item As Variant
Set AllCells = Worksheets("Positions").Range("A2:A150")
On Error Resume Next
For Each item In AllCells
Successor.cboPositionID1.AddItem item
Next item

As you can see I have set the range to be from A2 to A150 which assumes 148
records.

There is not currently 148 records (104) so I end up will a lot of empty
rows on the dropdown.
The number of records will be changing so it would be nice to get a current
count of rows on the sheet when the FORM "Initializes" and then only
populates that many rows on the FORM Drop down.

Any help here will be appreciated.

Thanks in advance
dave

Reply to this Message

JW - 26 Sep 2007 18:38 GMT

one way:
Dim bRow As Long
bRow=Cells(Rows.Count,"A").End(xlUp).Row
Set AllCells = Worksheets("Positions").Range("A2:A" & bRow)

> I am using the following code to populate a drop down on a FORM in Excel.
>
[quoted text clipped - 20 lines]
> Thanks in advance
> dave

Reply to this Message

Bob Phillips - 26 Sep 2007 18:40 GMT

Public Sub PopulatePositions()
Dim AllCells As Range, Cell As Range
Dim item As Variant
With Worksheets("Positions")
item = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Me.cboPositionID1.List = item
End With
End Sub

Signature

HTH

Bob

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

>I am using the following code to populate a drop down on a FORM in Excel.
>
[quoted text clipped - 20 lines]
> Thanks in advance
> dave

Reply to this Message

Dave - 26 Sep 2007 19:56 GMT

Thanks to you both for the replies.
used Bob's and it worked like a charm

dave

>I am using the following code to populate a drop down on a FORM in Excel.
>
[quoted text clipped - 20 lines]
> Thanks in advance
> dave

Reply to this Message