Setting ComboBox.RowSource through VBA

[logius]

There are two comboboxes:
CityBox <- Contains names of various cities
AddrBox <- Contains addresses of various locations

Both are located on a userform but AddrBox is disabled until an item in CityBox is selected. Each is being populated via the RowSource object which is pulling data from an Excel spreadsheet in this format:

City Name | # of Locations | Address 1 | Address 2 | Address3 | etc... |

Now the code is supposed to populate AddrBox once the user selects a city from CityBox. The problem is only the first address (Address 1) is coming up in AddrBox.

Private Sub CityBox_Change()
Dim StartPos, EndPos As String
Dim AddrTotal, Pos As Integer

    AddrBox.Enabled = True
    Pos = CityBox.ListIndex + 1

' Excludes initialization
    If Pos > 0 Then
        AddrTotal = CitySheet.Cells(Pos, 2).Value + 2
        StartPos = CitySheet.Cells(Pos, 3).Address
        EndPos = CitySheet.Cells(Pos, AddrTotal).Address
        AddrBox.RowSource = StartPos & ":" & EndPos
    End If

End Sub

CitySheet is a global variable that is assigned in the UserForm_Initialize routine and CityBox uses the RowSource to assign the values:

Set CitySheet = Worksheets(2)
CityBox.RowSource = "A1:A24"

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

Hi,

Coupla of problems. First, you ought to have the Sheet Name referenced as well as the range...
[tt]
Worksheets(2)!A1:A24
[/tt]
But even that's a problem. What is a user inserts a sheet before that one or changes the position? I always use the CodeName to name my sheets.

Second, your data layout is not a proper design. It can work for a VERY SMALL application. What if you have more than 254 addresses for a city?

A better data design would be...
[t]
City Address
Dallas 123 Main
Dallas 345 Oak
Phillie 1 Broad
[/tt]
There are a number of techniques for this type of data.

check here...

http://www.contextures.com/tiptech.html

for dynamic lists

I like to use MS Query via Data/Get External Data/New Database Query - Excel Files -- YOUR WORKBOOK ...

Query the sheet Selecting Distinct City for the City List Fill range

Query the sheet selecting Address with the City as criteria for the Address list fill range. This is a PARAMETER query -- set the parameter to a CELL on your query sheet.

Pretty simple!

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[logius]

No city will ever have more than 8 locations to keep track of. That probably should have been mentioned beforehand, sorry. As for referencing the worksheet and the case that a user enters in another worksheet, they ARE being referenced by name, I simply used "Worksheet(2)" as an example. I'll take a look at the website you referenced and see if anything helps.

Thanks.

----------------------------------------
If you are reading this, then you have read too far...