Excel VBA ActiveX Combobox

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
435
Hi,

I have a Activex Combobox (Combobox1) in Sheet1 and I am fetching unique value in combobox1 through recordset at click event when I am clicking dwopdown arrow on combobox1 value is showing but when I am selecting any value from that that value is not showing in the combobox1.

Code:-

Private Sub ComboBox1_Click()
Dim Myconnection As Connection
Dim Myrecordset As Recordset
Dim MyWorkbook As String
Set Myconnection = New Connection
Set Myrecordset = New Recordset

'Identify the workbook you are referencing
MyWorkbook = Application.ThisWorkbook.FullName
'Open connection to the workbook
Myconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyWorkbook & ";" & _
"Extended Properties=Excel 8.0;" & _
"Persist Security Info=False"

'Load the selected range into the recordset
Myrecordset.Open "Select Distinct [Region] from [Sheet1$B1:B30]", Myconnection, adOpenStatic
With ActiveSheet.ComboBox1
.Clear
Do
.AddItem Myrecordset![Region]
Myrecordset.MoveNext
Loop Until Myrecordset.EOF
End With
End Sub

Pleease help.

Thanks,
Kashif.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
The click event of a combobox only seems to trigger when you click on a choice in the dropdown, whereupon your code resets the contents of the list box, so its listindex is set to -1 again immediately you have clicked.
Surely you should be loading the combobox with options on some other event (another combobox's change event perhaps?).
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
That depends. It would seem you're loading this combobox at the last second because something somewhere changes what's to be found in Sheet1$B1:B30.
You could update the combobox on a sheet change event, restricted to when something in Sheet1$B1:B30 changes, or you could perhaps update it when you activate the sheet, or perhaps if the user uses another activex control on the sheet before using combobox1 you could use one of that control's event handlers.

As an aside, I don't know much about recordsets and the like, but I fancy that perhaps a
Myrecordset.Close
instruction somewhere would be tidy.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,104
Office Version
  1. 365
Platform
  1. Windows
As p45cal said, you need another event to fill the list such as GotFocus.

Keep in mind that a database field must be of the same variable type like a string or a number while an Excel column can have many different types. If you have mixed types, be sure to set the first cell in the data range to a string type or else code to skip the NULL values as I did.

Code:
Private Sub ComboBox1_GotFocus()
  Dim Myconnection As Connection
  Dim Myrecordset As Recordset
  Dim MyWorkbook As String
  Dim szConnect As String
  Dim a() As Variant
  Dim i As Long
  Dim v As Variant
  
  On Error GoTo EndErr
  
  Set Myconnection = New Connection
  Set Myrecordset = New Recordset
  
  'Identify the workbook you are referencing
  MyWorkbook = Application.ThisWorkbook.FullName
  'Open connection to the workbook
  If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & MyWorkbook & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & MyWorkbook & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
  End If
  Myconnection.Open szConnect
  
  'Load the selected range into the recordset
  Myrecordset.Open "Select Distinct F1 From [Sheet1$B:B] Order by F1 ASC;", Myconnection, adOpenStatic
  ComboBox1.Clear
  If Myrecordset.RecordCount < 1 Then GoTo EndNow
  On Error Resume Next
  For Each v In Myrecordset.GetRows
      'If first cell value is a number and a string is in another cell, _
      Null is the value of the string that causes an error.
      ComboBox1.AddItem CStr(v)
  Next v
  On Error GoTo EndErr
  
EndNow:
  Myrecordset.Close
  Set Myrecordset = Nothing
  Myconnection.Close
  Set Myconnection = Nothing
  Exit Sub
EndErr:
  If Not Myrecordset Is Nothing Then Myrecordset.Close
  Set Myrecordset = Nothing
  Myconnection.Close
  Set Myconnection = Nothing
  MsgBox Err.Description, , Err.Number
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,229
Messages
5,594,944
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top