Alternatives for the populating of a ComboBox

[quemuenchatocha]

Hi, I am working on a userform that calculates the distance between two cities (measured along a straight line) expressed in miles and kilometres. To make this possible, four ComboBoxes have been entered into the form, two of them record the State, while the remaining two contain the city corresponding to each selected State. For City #1:, the code works correctly, both for the city and the state, while for City #2:, when opening the form, the option appears blank as shown in the image. However, I can select the State, with its respective cities, but these are not displayed.

I have entered the following code and I can't find where the fault lies

Option Explicit
Public nStates as Integer, nCities as Integer

Sub PopulateStates()
Dim i As Integer
Sheets("Main").Select
Range("A1").Select
Sheets("Sheet1").Select
nStates = WorksheetFunction.CountA(Columns("E:E"))
nCities = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 2
For i = 1 To nStates
    UserForm1.state1select.AddItem Range("E1:E" & nStates).Cells(i, 1)
    UserForm1.state2select.AddItem Range("E1:E" & nStates).Cells(i, 1)
Next i
UserForm1.state1select.Text = Range("E1:E" & nStates).Cells(1, 1)
UserForm1.state2select.Text = Range("E1:E" & nStates).Cells(1, 1)
End Sub

I would be grateful if you could tell me what mistake I am making or give me some advice on how to solve this problem. Thank you very much for your attention.

ps: if necessary, in the following link I leave the book where you can find the complete code.

Workbook Example

 

[6StringJazzer]

The link for your file did not resolve. Make sure that you grant permissions--it is not enough to just copy the link.

Are you saying that you can select State #2 from the combobox dropdown list, but after you select it, the box is still blank? Have you done a test to see what value is selected after you do this? Just from what you showed so far, this may be some kind of display problem.

Please either correct the link to the file, or post all of the code for the entire UserForm. Thank you for using code tags!

 

[quemuenchatocha]

Dear @6StringJazzer my sincere apologies for the link, I did not realize it was not working, I send updated link. Regarding your question, it is possible to select the contents of the ComboBox of State#2, but when opening the form, the list of States does not appear, nor their corresponding cities, as shown in the image, I do not know where the error may be. Thank you for your attention.

Link updated

 

[6StringJazzer]

I am unclear as to what you mean: " it is possible to select the contents of the ComboBox of State#2, but when opening the form, the list of States does not appear"
How can you select the contents if the list of states does not appear?

I cannot reproduce this problem. When I execute Sub RunForm I get this. I click the STATE combobox and can see a list of states and select one. Then I can select a city.

 

[quemuenchatocha]

Dear @6StringJazzer thank you very much for your attention. Indeed, the list of States and their respective cities can be selected for City #2, what is not possible is that they appear when the UserForm is opened, that is to say, when the form is opened, only the name of the State and the city for City #1 appear visible, but if you look at the first image I published, the name of the State and its respective city for City #2 are not visible. That is my big concern, if with the current code that I have when opening the form why only allows me to see the State and the city for the City #1, but it does not happen the same for the State and the city in the case of the City #2.

 

[quemuenchatocha]

Basically what I need is that when I open the form, the State and the city for both cases (City #1 and City #2) are visible (text appears in both, for this case would be ALABAMA and Alexander City), but I can not get that result, and with the code I have only allows me to display the State and city for the City #1. What I am really looking for is that when I open the form, it looks like this [attached image].

I do not know if there is a possibility for the following code, that allows me to obtain the result I am looking for, which I think is where the error is occurring:

UserForm1.state1select.Text = Range("E1:E" & nStates).Cells(1, 1)
UserForm1.state2select.Text = Range("E1:E" & nStates).Cells(1, 1)

Thank you for your cooperation !

 

[6StringJazzer]

You have a bug caused by depending on which sheet is active at any given time. It is a best practice to always qualify sheet references and not depend on what sheet is active.

You are also initializing the data in UserForm1 outside of the form before it is activated. The best practice is to initialize the data in the form code, using either the Activate or the Initialize event. That is not causing this bug but would be a better design.

Here is the sub that initializes the form:

Sub PopulateStates()
'YOUR CODE GOES HERE
Dim i As Integer
Sheets("Main").Select
Range("A1").Select
Sheets("Sheet1").Select
nStates = WorksheetFunction.CountA(Columns("E:E"))
nCities = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 2
For i = 1 To nStates
    UserForm1.state1select.AddItem Range("E1:E" & nStates).Cells(i, 1)
    UserForm1.state2select.AddItem Range("E1:E" & nStates).Cells(i, 1)
Next i
UserForm1.state1select.Text = Range("E1:E" & nStates).Cells(1, 1)

When you reach the red line of code, you are changing the value of the state1select control. This fires the Change event and the following sub starts to execute:

Private Sub state1select_Change()
'PLACE YOUR CODE HERE
Dim i As Integer, j As Integer
UserForm1.city1select.Clear
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Range("A1").Select

For i = 1 To nCities
    j = 1
    If Range("A1:A" & nCities).Cells(i, 1) = UserForm1.state1select.Text Then
        Range("A" & i).Select
        Do While Not IsEmpty(ActiveCell.Offset(j, 0))
            UserForm1.city1select.AddItem ActiveCell.Offset(j, 0)
            j = j + 1
        Loop
        UserForm1.city1select.Text = ActiveCell.Offset(1, 0)
        Exit For
    End If
Next i
Sheets("Sheet1").Visible = False
End Sub

This event ends by hiding Sheet1. A hidden sheet cannot be the active sheet, so the active sheet is changed to Main by VBA.

Now the code in the other Sub resumes:

UserForm1.state2select.Text = Range("E1:E" & nStates).Cells(1, 1)
End Sub

But now Main is active, so the Range reference is now referring to cell E1 of Main, which is blank.

The correction for this is to make all of your Range reference explicitly qualified, such as

    UserForm1.state1select.AddItem Worksheets("Sheet1").Range("E1:E" & nStates).Cells(i, 1)
    UserForm1.state2select.AddItem Worksheets("Sheet1").Range("E1:E" & nStates).Cells(i, 1)

Never use ActiveSheet to determine which sheet is being referenced at a given time.

 

[quemuenchatocha]

Dear @6StringJazzer, thank you very much for your valuable help and dedication explaining me in detail the procedure and error of the code, I also appreciate the time you took to solve my doubts. I will take into account your wise advice and indications for future codes, you are very kind, a big hug!