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

strSQL = "SELECT tblIngredients.Ingredient FROM tblIngredients " & _
"ORDER BY tblIngredients.Ingredient"

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

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.

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?

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.

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.

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

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 (http://www.contextures.com/xlDataVal10.html).

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

http://img.photobucket.com/albums/v126/chergh/click-1.jpg

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.

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

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

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.