Excel Combo Box with External DataSource?

[MSAccessRookie]

I am trying to simulate an Acccess Combo Box on an Excel Spreadsheet. The effect would be to click on a cell and have a dropdown box with a list of choices appear. The User would then select one of the available choices. I am aware of how to do this via Excel ranges (both on the same WorkSheet, and on a separate Worksheet within the same SpreadSheet, but what I really need is to have the data source be an SQL Query to an SQL Server Table. The following Code updates a User Form with what I believe is the correct list of data, but since the User Form is not a part of the SpreadSheet, this was not deemed an acceptable solution at this time. Does anyone have any further ideas?

Public Sub GetIngredientList()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    With cn
        On Error GoTo ConnectionError
        .ConnectionString = "Provider=SQLOLEDB; " & _
        "Data Source=SQL; " & _
        "Initial Catalog=RDI;" & _
        "User ID=sa; Password=; Trusted_Connection=yes"
        .Open
    End With
 
    On Error GoTo GeneralSQLError
 
    [B][COLOR=green]strSQL = "SELECT tblIngredients.Ingredient FROM tblIngredients " & _[/COLOR][/B]
[B][COLOR=green]           "ORDER BY tblIngredients.Ingredient"[/COLOR][/B]
 
    rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
 
    rs.MoveFirst    ' Moves to the first record in the record set.
 
    ' Loops through each entry in the record set and adds the last name
    ' for each entry into the combo box.
 
    Do Until rs.EOF
        UserForm1.ComboBox1.AddItem rs!Ingredient
        rs.MoveNext
    Loop
 
    UserForm1.Show
 
    rs.Close    ' Closes the RecordSet.
    cn.Close   ' Closes the connection.
 
ExitHere:
    On Error Resume Next
    cn.Close: Set cn = Nothing
    Err.Clear
    Exit Sub
 
ConnectionError:
    MsgBox "Connection not propertly defined.", vbExclamation
    Resume ExitHere
 
GeneralSQLError:
    MsgBox "General SQL Error - " & Err.Description, vbExclamation
    Resume ExitHere
 
End Sub

 

[chergh]

Well the userform is part of the spreadsheet but explaining that to management is likely to be harder than the alternative.

Just plonk a combo box from the control toolbar on the spreadsheet and put the code above that you use to populate it in the click event of the combo box.

 

[MSAccessRookie]

Well the userform is part of the spreadsheet but explaining that to management is likely to be harder than the alternative.

Just plonk a combo box from the control toolbar on the spreadsheet and put the code above that you use to populate it in the click event of the combo box.

What you are saying makes sense, but when I double click the Combo Box to bring up the properties list in the VB Editor, I can find no "On Click" or Click Event to place the code behind. Am I missing something?

 

[Kryst51]

Right click on the combo box in the vb editor for the user form, then select view code and the screen should open up for the combo boxes on change event.

 

[MSAccessRookie]

Right click on the combo box in the vb editor for the user form, then select view code and the screen should open up for the combo boxes on change event.

Mea Culpa for not being clear in my previous posts. I was able to get the User Form to work, but Management prefers the Combo Box to be a part of the Spreadsheet. It is the Spreadsheet Combo Box that I cannot find the event for.

 

[Kryst51]

Mea Culpa for not being clear in my previous posts. I was able to get the User Form to work, but Management prefers the Combo Box to be a part of the Spreadsheet. It is the Spreadsheet Combo Box that I cannot find the event for.

Actually, I probably didn't read closely enough.

Is this jpg what you are looking for? on the control toolbox, next to view properties button is a view code button.

Edit: Oh, just realized too, that I am not sure you can add code directly to the combo box like you can in access. Here's an example of what I used for the on selection event of the worksheet. I remember when I was researching the best way to work with this spreadsheet like I wanted that this was the best solution I could find since I didn't know enough about VBA for excel to make userforms work for me:

'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboClassification As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True
 
    If Target.Locked Then
         'MsgBox "This is not an editable cell.", vbInformation, "Worksheet Protection"
         Range("$E$3:$F$3").Select
    End If
If Application.CutCopyMode Then
  'allow copying and pasting on the worksheet
  GoTo errHandler
End If
Set cboClassification = ws.OLEObjects("cmbClassificationLarger")
  On Error Resume Next
  With cboClassification
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errHandler:
  Resume exitHandler
End Sub

 

[Kryst51]

I reread your OP and I too wanted the same thing so I used a "before double click event" to make the combobox visible and to set the linked cell and all that stuff and pull in the list I wanted to use. THEN I went into the on selection change code. Let mek now if you would like to see it, I found it by google-ing here.

 

[chergh]

What you are saying makes sense, but when I double click the Combo Box to bring up the properties list in the VB Editor, I can find no "On Click" or Click Event to place the code behind. Am I missing something?

The click event exists for me in 2003

 

[chergh]

Though now I've tested it you might want to use the GotFocus event and the DropButtonClick event as click doesn't seem to fire.

 

[MSAccessRookie]

Though now I've tested it you might want to use the GotFocus event and the DropButtonClick event as click doesn't seem to fire.

It took me a while to figure it out, but I did find the Click event, and it works fine. Thanks to both you and Kryst51 for pointing me in the right direction.

HOWEVER:

As soon as I had it fixed, management indicated that they wanted to add Combo Boxes to enter each of the ingredients on the sheet (isn't that the way it always goes?). I decided that repeating the code would create excessive and unnecessary overhead, so I chose to do a little redesigning (see below). The result works fine but is a little slow. Is the approach appropriate for the task, or can someone suggest another approach?

---------------------------------------------------------------------

The sample Worksheet contained positions for 8 ingredients, but I have seen worksheets with many more. On the WorkSheet VB Page, I have 8 entries similar to the following:

Private Sub ComboBox1_GotFocus()
 
    GetIngredientList (ActiveSheet.ComboBox1)
 
End Sub

In a common code module, I now have this code

Public Sub GetIngredientList(ComboBoxName As ComboBox)
 
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    With cn
        On Error GoTo ConnectionError
        .ConnectionString = "Provider=SQLOLEDB; " & _
        "Data Source=SQL; " & _
        "Initial Catalog=RDI;" & _
        "User ID=sa; Password=; Trusted_Connection=yes"
        .Open
    End With
 
    On Error GoTo GeneralSQLError
 
    strSQL = "SELECT tblIngredients.Ingredient FROM tblIngredients " & _
             "ORDER BY tblIngredients.Ingredient"
 
    rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
 
    rs.MoveFirst
 
    Do Until rs.EOF
        ComboBoxName.AddItem rs!Ingredient
        rs.MoveNext
    Loop
 
    rs.Close
    cn.Close
ExitHere:
    On Error Resume Next
    cn.Close: Set cn = Nothing
    Err.Clear
    Exit Sub
ConnectionError:
    MsgBox "Connection not propertly defined.", vbExclamation
    Resume ExitHere
GeneralSQLError:
    MsgBox "General SQL Error - " & Err.Description, vbExclamation
    Resume ExitHere
 
End Sub

 

[chergh]

you could import the recordset into a hidden spreadsheet when the workbook is opened and then use that as the source for the combobox, this assumes that getting the recordset from the db is what is slowing things down.

You could also try turning screen updates off when the sub run though I wouldn't have thought this was causing the issue.

Private Sub ComboBox1_GotFocus()
 
Application.ScreenUpdating = False
    GetIngredientList (ActiveSheet.ComboBox1)
 Application.ScreenUpdating = True
End Sub

 

[MSAccessRookie]

you could import the recordset into a hidden spreadsheet when the workbook is opened and then use that as the source for the combobox, this assumes that getting the recordset from the db is what is slowing things down.

You could also try turning screen updates off when the sub run though I wouldn't have thought this was causing the issue.

Private Sub ComboBox1_GotFocus()
 
Application.ScreenUpdating = False
    GetIngredientList (ActiveSheet.ComboBox1)
 Application.ScreenUpdating = True
End Sub

I tried adding the suggested code for Application.ScreenUpdating, and did not notice any change. I am going to try the other suggestion, but am not sure it will work in the long run, as we are approaching 50,000 items in the Ingredient List. Is there a way to get it into an invisible combo box that loads whenever the screen is activated, and then transfer the values to each of the others as they are required? That should satisfy the need.