PDA

View Full Version : reference combobox as a variable



mshbhwn98
08-15-2014, 08:34 AM
This may seem like a simple question but I have having some problems with finding the solution.

I want to set a variable to the name of a ComboBox. (I have several ComboBoxes). Then use that to populate the ComboBox list. My ComboBox list comes from the result of a stored procedure in SQL. If someone could point me in the right direction i'd really appreciate it

Here is my code.


Public Sub LoadEDMs()
'Set variables
Dim cbx As OLEObject
Set cbx = ActiveSheet.OLEObjects("EDMImportEDMSelectCB") 'my comboBox is called EDMImportEDMSelectCB. At the moment I'm just using one but I will make it adaptable

'Open the connection
Dim cnn As New ADODB.Connection
cnn.ConnectionString = "Provider=sqloledb;Data Source=SRISD0004;Initial Catalog=PC_Tools_Binders_DQT;Integrated Security=SSPI;"
cnn.ConnectionTimeout = 0
cnn.CommandTimeout = 0
cnn.Open

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandText = "usp_SelectDatabases" ' set the stored proc
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnn

Dim Rst As New ADODB.Recordset
Rst.Open cmd.Execute
Rst.MoveFirst

With wksModeller.OLEObjects("cbx")
'With wksModeller.EDMImportEDMSelectCB - this code works but if I use the above line it does not
.Clear
Do
.AddItem Rst![Name]
Rst.MoveNext
Loop Until Rst.EOF
End With

'wksModeller.EDMImportEDMSelectCB.ListIndex = 0 - this code works but if I use the above line it does not
wksModeller.OLEObjects("cbx").ListIndex = 0

End Sub

lecxe
08-15-2014, 10:03 AM
Hi

Try instead


With wksModeller.OLEObjects("cbx").Object

Remark: you could also use a variable that references the combobox directly

snb
08-16-2014, 04:14 AM
I'd prefer:


Sub M_Access_query_snb()
' Reference to Microsoft ActiveX Data Objects 2.0 Library

With New Recordset
.Open "SELECT * FROM Q_test", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
ComboBox1.Column = .GetRows
End With
End Sub

mshbhwn98
08-18-2014, 03:28 AM
Hi

Try instead


With wksModeller.OLEObjects("cbx").Object

Remark: you could also use a variable that references the combobox directly

Hi,

I tried using the above suggestion but got the error: Run-time error '1004': The item with the specified name wasn't found.

The debugger select the changed line.

Do you have any suggestions?

Thanks

Aflatoon
08-18-2014, 05:08 AM
Try:

With cbx.Object