initialize userform code not working first time

[lcoreilly]

I wrote some code to fill a combobox when a userform is initialized.
I'm finding that the first time I load the form, the code does not
fill the combobox with the most recent values. If I close out of the
form and then re-open it, it works. Any idea what is going on and how
to fix this?

Thanks.

 

[Paul Robinson]

Hi
The userform initialize code would help. Better than guessing!
regards
Paul

 

[OssieMac]

Would you like to share the code that you are using to open the form plus
your UserForm_Initialize code and then we can attempt to acertain what the
problem might be.

 

[lcoreilly]

Here is the code:

Private Sub UserForm_Initialize()
lastrow = FindLastRow

Dim i As Long
Dim cell As Range
Dim Rng As Range

With ThisWorkbook.Sheets("data")
Set Rng = .Range("A5", .Range("A5").End(xlDown))
End With

For Each cell In Rng.Cells
With Me.ComboBox1
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
End With
Next cell

End Sub

Any insight or help is much appreciated.

 

[lcoreilly]

The FindLastRow function:

Private Function FindLastRow()
Dim r As Long
r = 5
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
r = r + 1
Loop
FindLastRow = r
End Function

 

[Paul Robinson]

Hi
Can't see a problem with the initialize code, apart from you probably
needing

lastrow = FindLastRow()

You need brackets when calling a function and assigning it to a
variable. Without Option Explicit turned on though, your code
lastrow = FindLastRow

will simply make lastrow an empty variable and since there is nothing
in the initialization code which uses lastrow (why is it in there??)
then that won't cause a problem.
That leaves the code which calls the form as the culprit...unless you
havn't shown us some of the initialize code (the bit that uses
lastrow) perhaps??

regards
Paul

 

[OssieMac]

Under test your code worked perfectly. Just to be sure, I closed Excel and
re-opened and it still worked.

A little tip. You can use the List property of the combo box to simply
assign the range value without using a loop. Note the Offset in assigning the
range to include both columns in the range variable.

Private Sub UserForm_Initialize()
'lastrow = FindLastRow

Dim i As Long
Dim cell As Range
Dim Rng As Range

With ThisWorkbook.Sheets("data")
Set Rng = .Range("A5", .Range("A5") _
.End(xlDown).Offset(0, 1))
End With

Me.ComboBox1.List = Rng.Value


End Sub