Forum Discussion
vba code assist to hide/show based on drop-down list selections
I need assistance with building a dashboard that will default to hide all rows based on "select role" and "select location" and will only produce results based when both drop-downs have changed. Can someone please assist me?
12 Replies
dthomp74 Please see my suggestion in the attached sample workbook. I do not use VBA but use array formula based on some assumptions:
1. The results to be shown are Contact names (if you want to have more columns to show, you can copy the formula in column J over and modify "Contact" named range to whichever named range you want to show)
2. Currently I only put the array formula in column J of Tab Dashboard from J6:J27, based on max possible results of Tab Data for each combination of Role and Location.
3. L4 is dropdown for Role and J4 is dropdown for Location (use Data Validation/List instead of Inserting controls with VBA)
4. The array formula in J6:J27 would be:
{=IFS(OR(ISBLANK($J$4),ISBLANK($L$4),AND($J$4="Select Role",$L$4="Select Location")),"",TRUE,IFERROR(INDEX(Contact,SMALL(IF($J$4&$L$4=RoleLocation,ROW(RoleLocation)-MIN(ROW(RoleLocation))+1,""),ROW(A1))),""))}
- Then press Alt + F11 to open the Microsoft Visual Basic for Application window.
Double click the current open sheet name in the VBAProject section to open the Code editor.
VBA code: hide or unhide columns based on drop down list selection. It is next to impossible for anyone to help you unless you upload a sample file and then describe what exactly you are trying to achieve.
Subodh_Tiwari_sktneer uploaded to original message.
You responded to @Subodh_Tiwari_sktneer's request with a full upload of the lists you're working with. But you didn't respond to his equally important request, that you "then describe what exactly you are trying to achieve."
It strikes me that it's possible that the new Dynamic Array functions might be able to achieve your task; they can do some pretty remarkable things with drop-down lists.....but we do need to know more precisely what it is that you're aiming to achieve.
