Hi all,
I have a worksheet containing multiple combo boxes, many but not all of which i want to hide on opening (only to be revealed subject to selections in other visible combo boxes as user completes form). The combo boxes are non-contiguous.
I previously used this code:
etc etc
Please Login or Register to view this content.
but this is repetitive and slow so i'm hoping there is a better way?
any help much appreciated
Thanks
You can use an array:
Please Login or Register to view this content.
Rory
If you know the index # of the dropdowns that you want to hide...
Something like...
To find out index# of each dropdown...Please Login or Register to view this content.
Please Login or Register to view this content.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks both!
CK76 i have used your code and it appears to be working perfectly but i'm getting na error message "unable to set the visible property of DropDown", but it appears to have worked? any ideas>?
thanks again
Hmm, I'm not sure. You may have Index# that's larger than any existing dropdown.
And you are welcome
can anyone help with this?
the macro is working in that it is hiding and unhiding the correct drop down boxes but then its throwing up this error message "unable to set the visible property of DropDown"?
Without sample of where this is occurring. Bit hard to help. As we'll need to guess at what exactly is going on.
If you did verify that the .DropDowns(index#) exist in worksheet. Then it's likely due to some setting. But we are not mind reader after all
To upload workbook, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.
I can't really upload the file as its sensitive and to strip it all out wouldn't leave much - I just tried the code on another workbook with no content and it works fine, so its not the code it must be something else to do with my workbook... its not protected. anything else I can look for? thanks again
Can't you remove all the data and just leave the code and dropdowns?
ok I've stripped out all the data, the second worksheet had all the details for the combo boxes but I've deleted most of it.
the code is in the worksheet section "on open"
thanks!
There doesn't appear to be a dropdown with index 78 on that sheet. Also, I'd recommend you loop through the array:
for example.Please Login or Register to view this content.
Last edited by rorya; 08-08-2019 at 10:53 AM.
Looks like you've hit limit of array elements that can be used at once to set visible state.
As well, you don't have DropDowns(78) in your workbook.
You can either break it into chunks or just loop over it. Personally, I'd just loop over each dropdown like below. Speed isn't that much different.
Please Login or Register to view this content.
Thanks Rorya, I've taken the 78 out and still the same issue
I tried the code in isolation with one box at a time incase there were any issues with the box references but same issue
the weird thing is it is giving the error message but when you click End it executes the code as expected?
You shouldn't get error if you loop (and exclude the non existent dropbox).
See attached. I had no issue.
Perfect! Thank you, you've saved me from endless hair pulling!
do you know what I was doing wrong? the code worked on a different sheet...?
It's due to 1 of two things, I'd wager.
1. Index was out of bounds for existing DropDowns in worksheet
2. Too many array elements were accessed at once (can't find documentation on in, but you can test yourself by increasing array element one by one).
If you are satisfied with answers provided, please mark the thread as solved by using thread tool found at top of your initial post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks