abhay_547
05-19-2010, 03:40 AM
Hi All,
I have populated a list box with the some data which is present in an sql data table and have set the liststyle of listbox in properties window to 1 because of which I am able to see the check boxes alongwith the names which got populated in my listbox. Now when i launch the userform by default all checkboxes are unselected I want them to be selected by default and also want to an option of "All" to my listbox alongwith other populated names.
Following is the code which I have so far.
Code pasted in background of userform :
Private Sub ComboBox1_Click()
Dim sSQL As String
Set adoRS = New ADODB.Recordset
sSQL = "SELECT DISTINCT Country FROM Region_Mapping WHERE Region = '" & ComboBox1.Value & "'"
adoRS.Open sSQL, ADOCn
ListBox1.Clear
Do While Not adoRS.EOF
ListBox1.AddItem adoRS(0)
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
End Sub
Public Sub LoadCombo()
Dim sSQL As String
Set adoRS = New ADODB.Recordset
sSQL = "SELECT DISTINCT Region FROM Region_Mapping"
adoRS.Open sSQL, ADOCn
ComboBox1.Clear
Do While Not adoRS.EOF
ComboBox1.AddItem adoRS(0)
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
End Sub
Private Sub UserForm_Initialize()
OpenDB
LoadCombo
End Sub
Below is the code which I have pasted in Module 1 :
Public ADOCn As ADODB.Connection
Public adoRS As ADODB.Recordset
Public gstrConnString As String
Public Sub OpenDB()
gstrConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=XXXXXXX;" _
& "Data Source=XXXXXXXXXXXX"
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = gstrConnString
ADOCn.Open gstrConnString
End Sub
Attached is the screenshot of userform for your reference.
Thanks a lot for your help in advance. :bow:
I have populated a list box with the some data which is present in an sql data table and have set the liststyle of listbox in properties window to 1 because of which I am able to see the check boxes alongwith the names which got populated in my listbox. Now when i launch the userform by default all checkboxes are unselected I want them to be selected by default and also want to an option of "All" to my listbox alongwith other populated names.
Following is the code which I have so far.
Code pasted in background of userform :
Private Sub ComboBox1_Click()
Dim sSQL As String
Set adoRS = New ADODB.Recordset
sSQL = "SELECT DISTINCT Country FROM Region_Mapping WHERE Region = '" & ComboBox1.Value & "'"
adoRS.Open sSQL, ADOCn
ListBox1.Clear
Do While Not adoRS.EOF
ListBox1.AddItem adoRS(0)
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
End Sub
Public Sub LoadCombo()
Dim sSQL As String
Set adoRS = New ADODB.Recordset
sSQL = "SELECT DISTINCT Region FROM Region_Mapping"
adoRS.Open sSQL, ADOCn
ComboBox1.Clear
Do While Not adoRS.EOF
ComboBox1.AddItem adoRS(0)
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
End Sub
Private Sub UserForm_Initialize()
OpenDB
LoadCombo
End Sub
Below is the code which I have pasted in Module 1 :
Public ADOCn As ADODB.Connection
Public adoRS As ADODB.Recordset
Public gstrConnString As String
Public Sub OpenDB()
gstrConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=XXXXXXX;" _
& "Data Source=XXXXXXXXXXXX"
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = gstrConnString
ADOCn.Open gstrConnString
End Sub
Attached is the screenshot of userform for your reference.
Thanks a lot for your help in advance. :bow: