running script when workbook opens problems
 

Hi,

I have a worksheet that accesss a database and displays the selected
record on the worksheet. The user selects the record by a msform
combobox which is populated with the ID of every record available. At
the moment I can only get the combobox to poulate when I press an
msForm command button. The code behind this button adds items to the
combobox.Both MSForm objects are on the worksheet not in a form.
What I would like to happen is for the combobox to be populated when I
open the workbook. To do this I put the code behind the refresh command
button in workbook_open() event. But I'm getting problems :o(

Because several scripts refresh the combobox e.g when a new record is
added/deleted etc I created a function 'ListRecipeCodes'
The input to the function is the combobox so the code behind the button
is:

ListRecipeCodes cboRecSel (where cboRecSel is the name of mycombobox)


ByRef argument Type mismatch error. Now I think I'm getting this error
because the sheet with the combobox isn't activated yet, so I've tried
putting Sheet(1).select before thee refresh code but I'm still getting
the error.

Can anyone help me please?

Thanks in advance.

Rich

Just in case my list fuction is the problem here it is:

Public Function ListRecipeCodes(combobox As Object)

Dim AdoCon As ADODB.Connection
Dim AdoRs As ADODB.Recordset
Dim strSQL As String
Dim strError


combobox.Clear
Application.Cursor = xlWait

strSQL = "SELECT DISTINCT " & ctsTblRecipe & "." & ctsNcRecipe &
ctiRecCodePos
strSQL = strSQL & " From " & ctsTblRecipe
strSQL = strSQL & " ORDER BY " & ctsTblRecipe & "." & ctsNcRecipe &
ctiRecCodePos

'Debug.Print strSQL

' Open the connection.
Set AdoCon = New ADODB.Connection
AdoCon.ConnectionString = ctsDB
AdoCon.Open

' Set the command text.
Set AdoRs = New ADODB.Recordset
AdoRs.Open strSQL, AdoCon, adOpenKeyset, adLockPessimistic,
adCmdText

AdoRs.MoveFirst

' Populate ComboBox with list of Recipe codes
Do Until AdoRs.EOF = True

If AdoRs(0) < "" Then
combobox.AddItem AdoRs(0)
End If

AdoRs.MoveNext
Loop

lbTidy:

AdoRs.Close
Set AdoRs = Nothing
Set AdoCon = Nothing
Application.Cursor = xlDefault

Exit Function

ErrorHandler:

strError = "List Recipe Codes Error"
strError = strError & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox strError, vbInformation
Resume lbTidy

End Function