Solved: Loading a listBox

[pcarmour]

Hi,

I have an excel user form called 'Sell_Order' with 2 Text Boxes (called 'Client' and 'Account_No') that take values from cells A1 and K1 on the active sheet and also a listBox called 'Holdings' that has 5 columns of data coming from a named range 'Absolute' on a worksheet called 'Holdings'
my problem is that the Boxes don't load on the first opening but if I close it and re-open it the 3 boxes load OK.

Here is the code I have used.
Range("A1").Select
Sell_Order.Client.RowSource = "A1"
Sell_Order.Account_No.RowSource = "K1"
If ActiveCell.Value = "ABSOLUTE INVESTMENT MANAGEMENT" Then
Sell_Order.Holdings.RowSource = "Holdings!Absolute"
End If
I have tried 'Initialize and Load' but still only loads on the second opening of the user form.

I have Windows 7 Professional and Excel 14.0.473.1000 (32 bit)
Any help would be much appreciated.
Regards,
Peter

[p45cal]

I think we'd have to see the workbook itself.
If it's any use, any code you want to run when the userform is first shown/loaded should be in the Initialize event.

[pcarmour]

Hi,
Thanks for your help in advance.
Here is my code so far.

Regards,
Peter

Private Sub New_SELL_Order_UserForm_Initialize()
Dim Client As String
Dim Account_No As listbox
Dim Holdings As listbox
Dim Sell_Order_Stock_Code As String
Dim Sell_Order_Stock_Name As String
Dim Sell_Order_ISIN As String
Dim Sell_Order_GTC As OptionButton
Dim Sell_Order_Recd_Time As Date
Dim Sell_Order_Amount As Integer
Dim Sell_Order_Limit As Integer
Dim Sell_Order_Instructions As String
Dim Sell_Order_Broker As ComboBox
Dim TextBox1 As String

' Call UserForm_Initialize
'Unload Sell_Order
'Load listbox2
'Initialize_Sell_Order
' Sell_Order_Initialize
'Sell_Order.ListBox1.AddItem ("A1")
'Sell_Order.ListBox1.AddItem Item

ActiveSheet.Unprotect
Application.ScreenUpdating = True
Sell_Order.Show
Dim oneControl As Object
For Each oneControl In Sell_Order.Controls
Select Case TypeName(oneControl)
Case "TextBox"
oneControl.Text = vbNullString
Case "OptionButton"
oneControl.Value = False
Case "ComboBox"
oneControl.Value = ""
End Select
Next oneControl





Range("A1").Select
Sell_Order.Client.RowSource = "A1"
Sell_Order.Account_No.RowSource = "K1"
If ActiveCell.Value = "ABSOLUTE INVESTMENT MANAGEMENT" Then
Sell_Order.Holdings.RowSource = "Holdings!Absolute"
End If
Load Sell_Order
End Sub

[p45cal]

Have you got two userforms?! One called
New_SELL_Order_UserForm
and the other
Sell_Order
??

If you only have one userform, what's it called?
You don't need explicitly to call the initialize event, it's triggered by showing the userform.

[pcarmour]

Hi, there should be just one - Sell_Order.

[p45cal]

Hi, there should be just one - Sell_Order.

I think I need to see a version of your workbook.

[pcarmour]

Hi,
I have attached my spreadsheet 'Test'.
If you go to tab Absolute and click 'New Sell Order' the user form opens empty, click cancel or close and then click New Sell Order again and it Fills.

This is an early stage development so some buttons/tabs are empty.
Thank you,
Regards,
Peter

[p45cal]

See attached where the two userforms' proper initialize events have been used.

[pcarmour]

Thank you yet again, it looks as if I was over complicating a simple show command.
Regards,
Peter.