Filling ListBox another excel file

[suomi]

Hello,

I'm preparing a User Form containing List Box that shall be filled with
items taken from another excel file. My macro checks first, if this
source xls file is opened, and if not, it is being opened. However, I
realised, that filling the list box works only when the source file is
already opened. If it is not, and when macro opens it, the list box
stays empty. Do you know how to cope with this? Here is my code: (as
you can see I experimented with 'wait' and 'activate' but it did not
solved the problem).

Set AvailableQuotes = Application.Workbooks("Available quotes.xls")
If AvailableQuotes Is Nothing Then
Application.Workbooks.Open FileAvailableQuotes
End If

'Application.Wait (Now + TimeValue("0:00:05"))
'AvailableQuotes.Activate

Set RangeForex = AvailableQuotes.Worksheets(1).Range("b3")
NumberOfFilesForex = AvailableQuotes.Worksheets(1).Range(Range("b4"),
Range("b4").End(xlDown)).Rows.Count

'MsgBox NumberOfFilesForex

For i = 1 To NumberOfFilesForex
Bond.lboxSpotForexAvailable.AddItem RangeForex.Offset(i, 0).Value
Next i


There is also an interesting thing about 'Activate' command. Without
this command, my macro worked only when it was run from VB editor. If
it was run by from sheet command button, macro did not work. Putting
the Activate command, solved the problem here.

 

[Phillip]

Phillip London UK
This code works for me
Put in a standard module

Sub GetData()
Dim Data As Variant
Dim Lastrow As Integer

On Error Resume Next
Windows("Available quotes.xls").Activate
If Err > 0 Then
Application.Workbooks.Open "Available quotes.xls"
End If
On Error GoTo 0

Lastrow = Worksheets(1).Range("b3").End(xlDown).Row
Set rangeforex = Worksheets(1).Range("b3").Resize(Lastrow - 2, 1)
Data = rangeforex.Value

Load Bond
Bond.lboxSpotForexavailable.List = Data
Bond.Show
End Sub