Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Initialize | Excel Programming | |||
Userform Initialize | Excel Programming | |||
UserForm initialize event run when UserForm is shown | Excel Programming | |||
userform initialize | Excel Programming | |||
initialize userform, using a sub | Excel Programming |