Solved: List Box not linking!!!

[IrishCharm]

Hi,

I have a list box which i add data into and you basically tick one box in each. But i cannot seem to be able to identify the actual box being ticked. Any helo would be great - the macro that runs is the big white button and in the macro "Compile_Spreadsheet" is where i am looking at pulling in the data that is ticked but it is not coming in correctly for example if i tick "EUR" it may bring back in "GBP" as this is the last ccy on its list.

0: Hit the white button to kick off the macro
1: Hit the Import All Data button.
2: Tick the boxes (1 in each section) that you want
3: Hit the Run Report button - this should bring you into the Compile_Spreadsheet macro for which those ticked variables are brought back but i cant seem to do this correctly.

[Bob Phillips]

You try to open a non-existant form in Workbook_Open.

[vba]

Sub Compile_Spreadsheet()

str_Customer = Frm_Upload.ListBox1.List(Frm_Upload.ListBox1.ListIndex)
str_PC = Frm_Upload.ListBox2.List(Frm_Upload.ListBox2.ListIndex)
str_Ccy = Frm_Upload.ListBox3.List(Frm_Upload.ListBox3.ListIndex)
str_Trad = Frm_Upload.ListBox4.List(Frm_Upload.ListBox4.ListIndex)
str_ReportTab = Frm_Upload.ListBox5.List(Frm_Upload.ListBox5.ListIndex)

End Sub
[/vba]

You are also adding a blank line to each listbox, but your routines could be much simpler anyway

[vba]

Private Sub ImportAllData_Click()

'' #### Currency #### ''
Call LoadListBox(Frm_Upload.ListBox3, 2)

'' #### Profit Centre #### ''
Call LoadListBox(Frm_Upload.ListBox2, 4)

'' #### Dealers #### ''
Call LoadListBox(Frm_Upload.ListBox4, 6)

'' #### Customers #### ''
Call LoadListBox(Frm_Upload.ListBox1, 8)

'' #### Report Tab #### ''
Call LoadListBox(Frm_Upload.ListBox5, 10)

End Sub

Private Sub CommandButton2_Click()

Call Compile_Spreadsheet

End Sub

Private Function LoadListBox( _
ByRef ThisList As MSForms.ListBox, _
ByVal DataCol As Long)
Dim StaticRows As Long
Dim VecData() As Variant
Dim tmpData As String
Dim NextData As Long
Dim i As Long

With ActiveSheet

StaticRows = .Cells(.Rows.Count, DataCol).End(xlUp).Row
ReDim Preserve VecData(1 To StaticRows - 1)

tmpData = .Cells(1, DataCol).Value
For i = 2 To StaticRows

If tmpData <> Cells(i, DataCol).Value Then

NextData = NextData + 1
VecData(NextData) = .Cells(i, DataCol).Value
tmpData = .Cells(i, DataCol).Value
End If
Next i

ReDim Preserve VecData(1 To NextData)
End With

With ThisList

.List = VecData
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectExtended
End With
End Function
[/vba]

and you really should give your controls meaningful names.

[IrishCharm]

Hi xld,

That works perfectly - i have also changed the code to incorporate your function above. Thanks for all your help appreciate it

Sarah