Issue with Listbox on Excel Userform.

[abhay_547]

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:

[Jan Karel Pieterse]

You can select them all like this:
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)
ListBox1.Selected(ListBox1.ListCount-1)=True
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing


End Sub

[abhay_547]

Hi Jan,

Thanks a lot for your reply. Now I am using the below code and by default it selects all the names which get populated in the list box.


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)
ListBox1.Selected(ListBox1.ListCount-1)=True
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing


End Sub
But can you help me to add an additional item to the listbox .i.e "All" alongwith with the country names which get populated in listbox and when I select "All" item, all countries should get selected in the list box.

Thanks a lot for your reply in advance. :bow:

[abhay_547]

Hi,

Finally I have added "All" item to the top of my listbox after populating the countries in the same. However when I launch the user form the "All" item's checkbox doesn't get selected by default, Apart from this the code needs to be modified in such a way that when I remove the selection of checkbox of "All" item all the country items checkboxes should automatically deselected and vice versa .i.e When I select the checkbox of "All" item all countries checkboxes should get selected.

Here 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
ListBox1.AddItem "All"
Do While Not adoRS.EOF
ListBox1.AddItem adoRS(0)
ListBox1.Selected(ListBox1.ListCount - 1) = True
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

Code pasted in Module1 :

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=XXXXXXXX"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = gstrConnString
ADOCn.Open gstrConnString
End Sub
Attached is the screenshot for your reference. Please help.

Thanks a lot for your help in advance.l:bow:

[abhay_547]

Hi All,

Did anyone get the chance to look at the above post. Please help..