PDA

View Full Version : Hide columns based on cell value



dgilbert
08-20-2014, 06:01 AM
I would like to hide multiple columns in a spreadsheet based on a cell value. I have attached the spreadsheet. In cell C2 a supervisor will enter his/her name. I would like the supervisor to only see columns that pertain to him/her based on the column values in row 8. All other columns will be hidden from his/her view. If C2 is blank then all columns in the spreadsheet will show.

Can you please provide me with the vba code to do this.

Thanks!

JKwan
08-20-2014, 09:06 AM
try this out, I put in a combox for you, so you don't need to type anything

dgilbert
08-20-2014, 12:17 PM
Thank you. This is working, the only problem is it is doing the opposite of what I want. It looks like the columns being hidden are the ones that I want to be displayed. I want the supervisor to be able to select his name in the box and then just see his columns.

Also, how do I get this code into the worksheet in my real spreadsheet? The one I attached in this forum is just a shortened copy.

Thank you so much!

JKwan
08-20-2014, 01:39 PM
Sorry, I was half asleep!
If you want to put into your workbook, do this
hit Alt-F11 to bring up your VBE
put the following into the ThisWorkbook

Private Sub Workbook_Open()
' Add names here
Me.Worksheets("Data Entry").cmbSupervisorName.List = _
Array("Celia Greiner", "Cheryl Hargens", "Cody McDonald", "Dennis Fish", "All")
End Sub


put this into Module1, you do this by adding a Module into your workbook first, otherwise Module1 will not be listed

Option Explicit
Sub ShowHideColumns()
Const NameRow As Long = 8
Dim DataEntrySheet As Worksheet
Dim sSupervisorName As String
Dim LastColumn As Long
Dim lColumn As Long
Dim ColLetter As String

Application.ScreenUpdating = False
Set DataEntrySheet = Worksheets("Data Entry")
sSupervisorName = Worksheets("Data Entry").cmbSupervisorName.List(Worksheets("Data Entry").cmbSupervisorName.ListIndex)

Cells.Select
Selection.EntireColumn.Hidden = False
LastColumn = FindLastColumn(DataEntrySheet, NameRow)
ColLetter = ColumnLetter(LastColumn)
If sSupervisorName <> "All" Then
For lColumn = 5 To LastColumn
If DataEntrySheet.Cells(NameRow, lColumn) <> sSupervisorName Then
DataEntrySheet.Columns(lColumn).Hidden = True
End If
Next lColumn
End If
Application.ScreenUpdating = True
DataEntrySheet.Range("A1").Select
Set DataEntrySheet = Nothing
End Sub
Public Function FindLastColumn(ByVal WS As Worksheet, lRow As Long) As Long
FindLastColumn = WS.Cells(lRow, Columns.Count).End(xlToLeft).Column
End Function
Public Function ColumnLetter(ByVal lngCol As Long) As String
'This function will return a Column Number to a Column Letter
' =ColumnLetter(10) This will return "J"
Dim vArr As Variant

vArr = Split(Cells(1, lngCol).Address(True, False), "$")
ColumnLetter = vArr(0)
End Function


now create a combobox on your Data Entry tab and name it cmbSupervisorName
from your VBE, double click onto the Data Entry list and put the code into it

Private Sub cmbSupervisorName_Change()
ShowHideColumns
End Sub


Hope I did not miss anything

dgilbert
08-21-2014, 07:34 AM
This does exactly what I needed. Thank you very much for your help!

holycow
08-21-2014, 03:20 PM
The Module 1 code can also be


Option Explicit
Sub ShowHideColumns()
Dim rng As Range, sup As String, Cell
Set rng = Range("E8:Z8")
sup = Sheets("Data Entry").cmbSupervisorName.Value
Application.ScreenUpdating = False
If sup = "All" Then
Columns("E:Z").Hidden = False
Else
For Each Cell In rng
Cell.EntireColumn.Hidden = (Cell <> sup)
Next
End If
ActiveCell.Select
Application.ScreenUpdating = True
End Sub

dgilbert
01-16-2015, 06:04 PM
I am now getting a "Run-time error 438 Object doesn't support this property or method" message when trying to open this spreadsheet. Do you know how to fix this problem?

Thank you.