Excel VBA ActiveX Combobox

[kashif.special2005]

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.

 

[p45cal]

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?).

 

[kashif.special2005]

Hi p45cal,

Thanks for quick reply, please guide me which event is suitable for that.


Thanks,
Kahiif

 

[p45cal]

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]

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.

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