compile error on a listbox

[gjp55]

I have some code that has an active X listbox passed into a function.
This works fine once the spreadsheet is open (function is called from a button click) but I would like the function to be called as soon as the spreadsheet is opened up without user interaction.

The problem I have is I get a compile error as the spreadsheet opens up - "ByRef argument type mismatch" with the listbox name highlighted in the code.

Does anybody know how to fix this ?

Thanks


Private Sub Workbook_Open()

Sheet1.Activate
Sheet1.Select

PopulateListBox "sp_getsp1", 5, ListBox1
'param 1 is the sp name being called
'param 2 is a cell reference where the data from the sp is being dumped
'param 3 is the name of the listbox being populated

End Sub

 

[Norie]

Hard to say without seeing the function/sub PopulateListbox and/or knowing what type of listbox it is.

Is it on a worksheet? A userform?

 

[gjp55]

Hi, its an activex list box and the function excpects a msforms.listbox as a parameter.
What is strange is that once the spreadsheet is open, I can call the same function (with the same parameters) and it works fine.

function expects

Public Sub AllNewCode(spName As String, CellStart As Single, ListBox As MSForms.ListBox)

......code