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
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