initialize userform code not working first time
 

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.

initialize userform code not working first time
 

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

On Apr 21, 7:57*pm, lcoreilly wrote:
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.

initialize userform code not working first time
 

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.

--
Regards,

OssieMac


"lcoreilly" wrote:

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

initialize userform code not working first time
 

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.

On Apr 21, 3:54*pm, OssieMac
wrote:
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.

--
Regards,

OssieMac



"lcoreilly" wrote:
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.
.- Hide quoted text -

- Show quoted text -

initialize userform code not working first time
 

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

On Apr 22, 6:58*am, lcoreilly wrote:
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.

On Apr 21, 3:54*pm, OssieMac
wrote:



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.

--
Regards,

OssieMac

"lcoreilly" wrote:
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.
.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

initialize userform code not working first time
 

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

On Apr 22, 11:58*am, lcoreilly wrote:
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.

On Apr 21, 3:54*pm, OssieMac
wrote:



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.

--
Regards,

OssieMac

"lcoreilly" wrote:
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.
.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

initialize userform code not working first time
 

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



--
Regards,

OssieMac


"lcoreilly" wrote:

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

On Apr 22, 6:58 am, lcoreilly wrote:
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.

On Apr 21, 3:54 pm, OssieMac
wrote:



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.

--
Regards,

OssieMac

"lcoreilly" wrote:
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.
.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

.