For groups of checkboxes I know a fairly easy way of turning off all selections upon opening.
But with that you target the checkboxes, which have a certain name or all checkboxes in general.
The options in a listbox don't really have their own names that you can target in VBA (as far as I am aware).
So what code would I need to turn off all selections made in List Box 1, 2&3?
Hi,
There shouldn't be anything selected in a listbox on first opening so I'm not sure why you need code?
Don
Please remember to mark your thread 'Solved' when appropriate.
Erm... I just opened my file that I saved with a few options selected and when I close it and open it again, it still has those options selected...
I want to be able to start with a clean slate every time I open it.
Because one of the listboxes has more options that can fit on a screen, so scrolling down every time to check if everything is deselected to start with is something I would rather not have to do.
But I also would like an option for users to be able to select or deselect all of a particular listbox... Which I was hoping to be able to derive from this first option :P
Last edited by ruub7; 06-08-2017 at 03:26 AM.
Is the listbox on a worksheet, or a userform?
On a worksheet
That is strange- I cannot reproduce that in 2010. All selections are gone upon reopening the workbook.
It also happened with my previous project, What I did I add a clear checkbox button.
I add this code
Please Login or Register to view this content.
Unfortunately, that doesn't work, because they're form control listboxes, not activeX...
(I am a noob to VBA, but it seems that you have written a code that sets all checkboxes to false, I want a code that deselects all options from a listbox, wether that is controlled by a checkbox or a button doesn't really matter to me)
Last edited by ruub7; 06-08-2017 at 08:57 AM.
Aha- that explains it. You can use this to deselect all items in a form listbox
Please Login or Register to view this content.
This gives a really strange result, it somehow only works for the last listbox, the other listboxes still keep their selections upon opening.
(I have added a tiny bit to your code to get it to trigger upon opening and a specific sheet, but that shouldn't interfere with your code to clear the listbox selections...)
Please Login or Register to view this content.
If there is more than one listbox, you need to loop through them all
Please Login or Register to view this content.
Ah, the noobishness from me surfaces again
Thnx
But the thing is, with your initial code, wether I change the n=1 to for example n=4, it still clears the selections from the same listbox...
So what do I need to change to get it to target different listboxes? (different listbox numbers)
The n variable is for looping through the items in a listbox, not for determining which listbox. The code I posted a minute ago will loop through all listboxes on that sheet.
yup, I know and that one works like a charm now, thnx for that.
but what do I need to change to target a specific listbox, for example to select/deselect all items in 1 listbox only, by pressing a simple button?
Refer to it by name or number
orPlease Login or Register to view this content.
Please Login or Register to view this content.
Hmmz, yes, your code works if I only want to be able to set everything to false.
But I would like to be able to set it either all false or all true based on a checkbox.
I though this would make it work:
But it doesn't, because now it sets everything to true, both when you check and when you uncheck the checkbox that is linked to this macro...Please Login or Register to view this content.
What am I doing wrong?
Last edited by ruub7; 06-09-2017 at 07:46 AM.
A Form checkbox actually returns xlOn (1) or xlOff (-4146). Any non 0 number will implicitly convert to True as a boolean, so you need to use something like this
Please Login or Register to view this content.
Thnx, really usefull to know that for the future.
And it all works like a charm now!
Crap, now a problem has surfaced with a different part of my code.
I have a part of a code that copies the selected items from the listboxes to another sheet which I use to apply the filter, but the checkbox that can now select/unselect everything visually, does not translate to all items being added/deleted from the filter sheet...
This is the code I have for the to copy the selected items to the filter:
Please Login or Register to view this content.
I would recommend that you move the processing code to a separate routine which you may then call from both controls
You will then require a separate macro for the listbox itselfPlease Login or Register to view this content.
and a similar routine for the checkbox but it must specify the name of the listbox rather than using Application.CallerPlease Login or Register to view this content.
Erm... I have linked the listboxes to the seperate listbox_click macro, but now the filter doesn't update at all anymore...
As I cannot see exactly what you have done, I cannot really comment as to why it would not be working. Perhaps you could provide the workbook?
I had to do some serious deleting and editing to be able to provide a workbook, so I really hope I didn't delete too much.
But I think this should be enough for you to figure out what is wrong.
Last edited by ruub7; 06-12-2017 at 06:55 AM.
Your listbox name is not Listbox 1, Listbox 2 or Listbox 3, so the ListBox_Select routine doesn't do anything with the array it creates. Did you rename the control?
That is odd, previously I had this problem, but then the exact opposite.
I had renamed the listboxes, but the the code still needed to be List Box 1, 2 etc. in order to make it work.
And now somehow it does accept the names that I gave them...
Oh well, it works now.
And how do I get the checkboxes to work?
Do I need to make a seperate sub for each of the checkboxes, to call the select all function for that specific checkbox/listbox combo + the auto update thing of the filter?
Or is there a simpler way to achieve that for multiple checkboxes that control different listboxes?
If you use a naming convention for the checkboxes that would allow your code to identify the listbox to control, then you could use the same macro for all of them.
Everything seems to be working properly now.
Thnx for all your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks