|
How to limit columns that display based on selection of a dropdown value
New User to this this site. Great site, have found a lot of great information.
Have not been able to find how to do this: I have a Worksheet that I want to limit the columns that display based on a value selected in a dropdown list. This sheet is a worksheet that data entry will be completed on. The worksheet currently only contains a header row. The value in the dropdown list only contains two values: ValueA, ValueB The dropdown list was created using data validation, Allow list, and typing in ValueA, ValueB. Column B Contains the dropdown list of ValueA, ValueB What I want to do is: If ColumnB contains ValueA, then display columns: A through H and K If ColumnB contains ValueB, then display columns: A through C, I and J How can I accomplish this? Thanks for the assistance as I am stumped. HRwilkers |
How to limit columns that display based on selection of a dropdown value
Copy this code into the Sheet that is used (ALT+F11, then select the proper
sheet on the left pane, then copy the code on the right pane. Note that the code can be more compact as we can hide/display contiguous columns in one line of code, but I voluntarily made it extensive for maintenance purposes. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$B$1" Then Exit Sub End If Select Case Target.Value Case "ValueA" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = False Columns("E").Hidden = False Columns("F").Hidden = False Columns("G").Hidden = False Columns("H").Hidden = False Columns("I").Hidden = True Columns("J").Hidden = True Columns("K").Hidden = False Case "ValueB" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = True Columns("E").Hidden = True Columns("F").Hidden = True Columns("G").Hidden = True Columns("H").Hidden = True Columns("I").Hidden = False Columns("J").Hidden = False Columns("K").Hidden = True End Select End Sub -- Message posted via http://www.officekb.com |
This works, but only for one cell. I need it to work for the entire column from B2 down. My guess is I need to use a range and modify this line, but can not figure out what to modify it with:
If Target.Address < "$B$1" Then How do I modify this to apply to all of column B from B2 down? Thanks. Hrwilkers |
How to limit columns that display based on selection of a dropdown value
This works, but only for one cell. I need it to work for the entire
column from B2 down. My guess is I need to use a range and modify this line, but can not figure out what to modify it with: If Target.Address < "$B$1" Then How do I modify this to apply to all of column B from B2 down? Thanks. I'm not sure I understand what you mean by "apply to all of column B from B2 down". Do you mean you want a ValueA or ValueB setting in ANY cell of column B (except B1) to trigger the hiding of the columns you indicated? If so, what about when some of the cells in column B are set to one value and the remaining cells are set to the other value.. which cell governs the hiding operation? Can you explain what you are trying to do in a little more detail? Rick |
Completed, Thanks for the Assistance. At first when I tried this I was getting a Run Time Error "13", I then added an If Error statement and that got rid of the error.
The code below will Display certain columns based on the value in column C. If you click on a blank cell, then it displays all columns. If you click on the header at the top, then it displays all columns, now for the code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B:$B" Then Exit Sub End If On Error Resume Next Select Case Target.Value Case "" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = False Columns("E").Hidden = False Columns("F").Hidden = False Columns("G").Hidden = False Columns("H").Hidden = False Columns("I").Hidden = False Columns("J").Hidden = False Columns("K").Hidden = False Case "(Header Value)" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = False Columns("E").Hidden = False Columns("F").Hidden = False Columns("G").Hidden = False Columns("H").Hidden = False Columns("I").Hidden = False Columns("J").Hidden = False Columns("K").Hidden = False Case "(Value A)" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = False Columns("E").Hidden = False Columns("F").Hidden = False Columns("G").Hidden = False Columns("H").Hidden = False Columns("I").Hidden = True Columns("J").Hidden = True Columns("K").Hidden = False Case "(Value B)" Columns("A").Hidden = False Columns("B").Hidden = False Columns("C").Hidden = False Columns("D").Hidden = True Columns("E").Hidden = True Columns("F").Hidden = True Columns("G").Hidden = True Columns("H").Hidden = True Columns("I").Hidden = False Columns("J").Hidden = False Columns("K").Hidden = True End Select End Sub Thanks to all who assisted. This was done in Excel Version 2002. Quote:
|
Sorry, It should read Column "B", NOT Column "C" as above. Jaosn
Quote:
|
| All times are GMT +1. The time now is 06:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com