Forum Discussion
Using a drop down to hide/unhide columns in a table (VBA Code)
Michael1835 To show/hide columns based on a selected employee, first open Name Manager and define a named range for the list of employees (referring to the applicable column headers). For example, define a new name called Employee_List that refers to: =Sheet1!$I$6:$DD$6. Next, create a Data Validation List in cell G6, with Source: =Employee_List. Then, open the Visual Basic Editor and use the following code in the Worksheet_Change event of the applicable worksheet module (Sheet1, in this example):
Private Sub Worksheet_Change(ByVal Target As Range) Dim rgEmployee As Range Set rgEmployee = Me.Range("G2") If Not Intersect(Target, rgEmployee) Is Nothing Then Dim rgList As Range, str As String, n As Variant Set rgList = Range("Employee_List") str = rgEmployee.Value n = Application.Match(str, rgList, 0) If IsError(n) Then rgList.EntireColumn.Hidden = False Else rgList.EntireColumn.Hidden = True rgList.Cells(, n).EntireColumn.Hidden = False End If End If End SubTo filter rows based on the "Check Training Status" selection in cell D2, I highly recommend looking into the Range.AutoFilter method, which will vary slightly depending on if your data is setup as a worksheet range or an Excel table (ListObject). As an example, the following code demonstrates how this could be achieved with an Excel table (ListObject), where the "Training Records out of Date" and "Retraining Required" columns are part of the table:
Sub CheckTrainingStatus() Dim tbl As ListObject, x As Long, y As Long Set tbl = Sheet1.ListObjects("Table1") x = tbl.ListColumns("Training Records out of Date").Index y = tbl.ListColumns("Retraining Required").Index With tbl.Range Select Case Sheet1.Range("D2").Value Case "Refresh" .AutoFilter Field:=x .AutoFilter Field:=y, Criteria1:="<>0" Case "Obsolete" .AutoFilter Field:=x, Criteria1:="<>0" .AutoFilter Field:=y Case Else 'All or blank .AutoFilter Field:=x .AutoFilter Field:=y End Select End With End SubTo apply the filter to the selected employee only, modify the worksheet formulas used in columns DE and DF to reference the matching column only. For example, if the original formula in cell DE7 was:
=SUM(I7:DD7)...you could change it to:
=IF(ISBLANK($G$2), SUM(I7:DD7), INDEX(I7:DD7, MATCH($G$2, Employee_List, 0)))Then, modify the Worksheet_Change event to call CheckTrainingStatus as needed. For example:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rgEmployee As Range Set rgEmployee = Me.Range("G2") If Not Intersect(Target, rgEmployee) Is Nothing Then Dim rgList As Range, str As String, n As Variant Set rgList = Range("Employee_List") str = rgEmployee.Value n = Application.Match(str, rgList, 0) If IsError(n) Then rgList.EntireColumn.Hidden = False Else rgList.EntireColumn.Hidden = True rgList.Cells(, n).EntireColumn.Hidden = False End If Call CheckTrainingStatus ElseIf Not Intersect(Target, Me.Range("D2")) Is Nothing Then Call CheckTrainingStatus End If End SubNote: this is an example only, intended to help point you in the right direction. If your data is not formatted as an Excel table (ListObject), additional steps may be required to identify the AutoFilter Range and/or the Field Numbers of the columns to be filtered.
The dropdown list can use the column headings themselves as the data validation list. (And if those data column headers of people's names are followed by one or more empty columns, you can include them in the validation list to use the resulting "blank" entry to reset visibility, as my code will do.)
Put the following code into the Worksheet_Change event handler for the worksheet that contains the cell with the dropdown list. (I assumed that the cell with the dropdown list is on the same worksheet as the data whose visibility you are controlling. If it is on a different worksheet, then the Range references in lines 18-21 will need to be qualified appropriately.) Change the Const values as is appropriate for the structure of your worksheet(s).
Const strCONTROLLING_CELL_ADDR = "F2"
Const strCONTROLLED_COLUMNS = "HIJKLMN"
Const strROW_WITH_COL_HDRS = "4"
Dim strSelection As String
Dim in4RelativeColumn As Long
Dim strColumn As String
'---- Show/hide worksheet columns depending on the selected
' column heading.
If Not Intersect(Target, Range(strCONTROLLING_CELL_ADDR)) Is Nothing Then
strSelection = Range(strCONTROLLING_CELL_ADDR).Value
Application.ScreenUpdating = False
For in4RelativeColumn = 1 To Len(strCONTROLLED_COLUMNS)
strColumn = Mid$(strCONTROLLED_COLUMNS, in4RelativeColumn, 1)
' --
If strSelection = "" _
Or Range(strColumn & strROW_WITH_COL_HDRS).Value = strSelection Then
Range(strColumn & "1").EntireColumn.Hidden = False
Else
Range(strColumn & "1").EntireColumn.Hidden = True
End If
Next in4RelativeColumn
Application.ScreenUpdating = True
End If
SnowMan55 thanks for your reply. I have inputted the code but it doesn't appear to work at the moment.
The controlling drop-down is on the same worksheet, this is how it looks with the rows and columns visible:
Here is the code with the const values updated:
A quick query, the rows end at DD but will excel understand "IJKLMNOPQRSTUVWXYZAAABAC etc..."?
Sorry I also need to add another drop down that will hide rows. Here is the layout:
The drop down in D2 has the following options:
"All" - Hide nothing
"Refresh" - hide rows that contain 0 in column DF
"Obsolete" - hide rows that contain 0 in column DE
I would like this to work in conjunction with the drop down for employee and on its own. So you can check for refresher/obsolete training for all or for a specific employee.
I understand that this is probably not possible as rows for other employees will show if refresher/obsolete training exists. So what would be most useful, if all is not achievable, is to be able to filter refresher/obsolete training for all employees.
Going back to my original query, filtering columns would work best if rows that do not contain any data are also hidden when the employee is select. I realise this I may be asking to much of excel here but I am new to VBA and I'm interested to find out it's capabilities and limits.
So to summarize:
Select employee, columns of other employees are hidden and rows that do not contain data are also hidden.
And/or
Select status of training and hide rows based on values in column's DE & DF or hide nothing for "All".
<< I have inputted the code but it doesn't appear to work at the moment. >>
When your car does not work, is that all the information you tell the repair shop? I have to assume that the code hid columns that you did not expect (and hid nothing after column Z), because…
No, Excel (VBA) will not "understand" the intended use of the letters, so it does not know to use one character up through Z and then use two characters. (It's not Artificial Intelligence.) I'm surprised that you want to handle as many as 100 employees (in columnar form); the worksheet structure seems backwards. But this data would usually be best stored in a database anyway, not a spreadsheet.
The most straightforward way is to allow for two characters for each column identifier (permitting you to use this on many more columns, if needed), and trim any space from each pair of column letters. To do that, replace the previously-assigned Const statement for strCONTROLLED_COLUMNS with the multi-line one below, and replace the first two lines of the For loop with lines 6-7 shown here:Const strCONTROLLED_COLUMNS = "I J K L M N O P Q R S T U V W X Y Z " _ & "AAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ" _ & "BABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ" _ & "CACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZ" _ & "DADBDCDD" For in4RelativeColumn = 1 To Len(strCONTROLLED_COLUMNS) Step 2 strColumn = Trim$(Mid$(strCONTROLLED_COLUMNS, in4RelativeColumn, 2))
To your new request, I'm not going to bother with code to suppress empty rows. You should not be including empty rows inside your data anyway. But if you are "interested to find out [VBA's] capabilities and limits", you'll study the code below enough to glean the necessary changes to make that happen.
Just include the following block of code inside the same event handler (Worksheet_Change), after the existing code. The hiding of rows is independent of the hiding of columns.Const strTRAINING_STATUS_CELL_ADDR = "D2" Dim in4WkshtRow As Long Dim vntCellContent As Variant '---- Show/hide worksheet rows depending on the selected "training ' status". If Not Intersect(Target, Range(strTRAINING_STATUS_CELL_ADDR)) Is Nothing Then strSelection = Range(strTRAINING_STATUS_CELL_ADDR).Value in4WkshtRow = Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'finds the last row with data Application.ScreenUpdating = False For in4WkshtRow = in4WkshtRow To Val(strROW_WITH_COL_HDRS) + 1 Step -1 If StrComp(strSelection, "All", vbTextCompare) = 0 Then Range("A" & in4WkshtRow).EntireRow.Hidden = False ElseIf StrComp(strSelection, "Refresh", vbTextCompare) = 0 Then vntCellContent = Range("DF" & in4WkshtRow).Value If IsEmpty(vntCellContent) Then '...ignore it. (Unfortunately, an empty cell passes the ' following IsNumeric test.) ElseIf IsNumeric(vntCellContent) Then If vntCellContent = 0 Then Range("A" & in4WkshtRow).EntireRow.Hidden = True End If End If ElseIf StrComp(strSelection, "Obsolete", vbTextCompare) = 0 Then vntCellContent = Range("DE" & in4WkshtRow).Value If IsEmpty(vntCellContent) Then '...ignore it. (Unfortunately, an empty cell passes the ' following IsNumeric test.) ElseIf IsNumeric(vntCellContent) Then If vntCellContent = 0 Then Range("A" & in4WkshtRow).EntireRow.Hidden = True End If End If End If Next in4WkshtRow Application.ScreenUpdating = True End IfNote that by design, the code does not unhide rows that were hidden for Refresh when Obsolete is selected, nor vice versa. Only All unhides (but again, you can figure out how to change that).
