Excel Populate Combo Box with Dynamic SQL Values

[sardine53]

Hello,

Excel 2013, SQL 2008R2

I am hoping this is the right forum and someone could point me in the right direction:

I have a Stored Procedure with a "Customer" parameter linking to an excel spreadsheet.

So far, I have an input box in Excel where the user can type the value and the command button will pull up the proper results:

Private Sub CommandButton1_Click()

Dim Customer As String

Member = Sheets("Sheet1").Range("K8").Value

With ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").OLEDBConnection
.CommandText = "EXEC dbo.usrsp_S_Customer_Census '" & Customer & "'"
ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").Refresh
End With
End Sub

My goal is to use a Combo Box for the user to select a value. I would like to use the stored procedure or SQL table to bring in the distinct values. As there will be new "Customer"s added frequently it needs to be a dynamic list.

How would I best accomplish this?

Thanks in advance for any assistance!

 

[sardine53]

Private Sub ComboBox1_Change()

Dim Customer As String

Member = Sheets("Sheet1").Range("K8").Value

With ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").OLEDBConnection
.CommandText = "EXEC dbo.usrsp_S_Customer_Census '" & Customer & "'"
ActiveWorkbook.Connections("ABCWEB2 SQLReports sysdiagrams").Refresh
ComboBox1.ListFillRange = Sheet1.[Table_ExternalData_1].Member

End With
End Sub

 

[SkipVought]

...and is your combobox an MS Forms control or an ActiveX control?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sardine53]

ActiveX

 

[SkipVought]

Try this...

ComboBox1.ListFillRange = Sheet1.[Table_ExternalData_1[Member]].Address

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I should have been more observant.

Member has only one value; the value in K8?

That does not make sense for a Combobox or am I missing something?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sardine53]

Member is only one column (not Member plus ID or something else)
I am expecting the drop-down to pull up the Member name and associated other columns from the stored procedure.

I was trying to convert the input box (which worked) to a drop down.

I'm probably the one that's missing something! I thought this would be simple.

 

[SkipVought]

Some suggestions to try...

ComboBox1.ListFillRange = Sheet1.[Table_ExternalData_1[Member]].Address

Or

ComboBox1.ListFillRange = Sheet1.[Table_ExternalData_1].Address

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and are you sure the Column Heading in your table has no leading or trailing SPACES?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sardine53]

Tried both with no luck. No leading or trailing spaces in the Column Heading.

Maybe I missing something else? I can still bring in the values using the text box with my initial code/post.

 

[SkipVought]

Can you upload a representative version of your workbook?

Don’t need the query connection but do need the data/table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]