Bulk Clearance of Listbox Checkbox Values

[BrianJN1]

Using a Database I am able to create an Output list from records checked in a Listbox.

The Command Button “Copy” reads the Listbox Selections and creates an Output in Column G; Columns H & I are displayed through the VLOOKUP function.

The code for “Copy” is:

Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
Dim lItem As Long
Dim YItem As Long
YItem = 0
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
Range("G" & YItem + 1).Value = (ListBox1.List(lItem))
YItem = YItem + 1
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

All of the above is fine.

My problem is to clear the valuse of the checkboxes in the Listbox with a macro attached to “Clear” button. I can clear Column G with a simple “Columns(“G:G”).ClearContents” statement. Now that database has some 130 records.

 

[Micron]

So you want to use a command button click event to clear all selections?
Try

Dim lstBox As Object
Dim i As Integer

Set lstBox = Sheets("yourSheetNameHere").ListBox1
For i = 0 To lstBox.ListCount - 1
     If lstBox.Selected(i) = True Then lstBox.Selected(i) = False
Next

 

[BrianJN1]

Exactly what I needed. From everything that I had researched nothing was giving me the syntax of that IF statement.

 

[Micron]

Glad I could help & thanks for the recognition. I forgot to mention that you might be interested in getting values for your sheet from the listbox itself, although if you are happy with using formulas for that, don't change what works. Just an FYI for Excel:
lstbox.column(0,0) gives you what is in the first column (0) and first row (0)
lstbox.column(1,0) gives you what is in the second column and first row

Probably easier to keep doing what you're doing, but it may come in handy when there are so many elements that a formula would be a nightmare.

 

[BrianJN1]

Now that extra tip caused me a little reflection, I knew what you meant but it took a few moments to realise how to implement.

I replaced the above FOR loop with:

For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
YItem = YItem + 1
Range("G" & YItem).value=ListBox1.Column(0,lItem)
Range("H" & YItem).value=ListBox1.Column(1,lItem)
Range("I" & YItem).value=ListBox1.Column(2,lItem)
End If
Next​

I have a rather complicated Workbook which utilises a number of Listboxes with exactly the same format and content other than checkboxes. I believe I can use this tip to make my future life with that project to my advantage.

Thanks.

 

[Micron]

Here's a couple more tips:
- you don't need .Value there as it is the default property. Not always true, but most of the time it applies.
- paste/write code within code tags (vba button on posting toolbar) to maintain indentation and promote readability. It will look like what I put in post 2.

 

[BrianJN1]

Apologies for the code format, it's been awhile since I've been here, but then, I should have known better.
Re: ".value" I have come across other instances where just the range("**") has been sufficient. Thank you.