Bulk Clearance of Listbox Checkbox Values

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
611
Using a Database I am able to create an Output list from records checked in a Listbox.
Picture1.png
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
So you want to use a command button click event to clear all selections?
Try
VBA Code:
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
 
Upvote 0
Solution
Exactly what I needed. From everything that I had researched nothing was giving me the syntax of that IF statement.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,826
Members
452,673
Latest member
LaMiaAvy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top