initialize userform code not working first time

  • Thread starter Thread starter lcoreilly
  • Start date Start date
L

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.
 
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.
 
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.
 
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
 
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
 
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
 
Back
Top