kashif.special2005
Active Member
- Joined
- Oct 26, 2009
- Messages
- 443
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.
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.