Excel automatically hiding certain columns

TanyaD

New Member
Joined
Feb 15, 2018
Messages
17
Hi,

Hoping you can help me. I am completely new to VBA but definitely willing to give it a try and I suspect it may be the only option to do what I need.

I have a large detailed calendar schedule for seven staff (each day has a column for each staff member). I'm hoping I can include a button for each staff member so when I press that button it only shows the columns for that staff member.

I'm guessing that to only show for staff1 the code would say something along the lines of if row 2 (the row that staff names are in) is "staff2" or "staff3" or "staff4" or "staff5" or "staff6" or "staff7" then hide. Which would then only leave Staff1's columns showing?

Greatly appreciate any help on this.

Cheers,

Tanya
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to the Board!

I think that in order for us to help out, it is important for us to have a clear understanding of the structure of your data. While you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html (also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question).

Also, using VBA, it may be possible to capture the login ID of your user, and use that to automatically determine which columns should be hidden automatically, if we can tie their login ID to the appropriate columns on the sheet.
Here is a thread which discusses how to get those values, and exactly what they are: https://www.mrexcel.com/forum/excel-questions/505370-application-username-environ-username.html
 
Upvote 0
Hi Joe!

Thanks for the links on how to post screen images - hopefully it will work for me this time, crashed just before when I posted it, though it did work in the test forum.

The login ID suggestion probably wouldn't be suitable - sorry that's my fault for not explaining it particularly well. In terms of wanting a button for each staff member, the schedule spreadsheet is just for the one person's use - to see the schedule for each employee separately.

If yourself or others can help in suggesting what VBA code I should attach to each of the buttons in order to see just that staff member's columns (essentially hiding all the other staff) that would be fantastic. - Or if you think there would be a better approach. The VBA code would probably have to apply across all the columns, as the number of active columns will continue to grow as more dates are added to the spreadsheet (6 columns per day).

Thanks again,

Tanya

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1Mon 12 FebTue 13 FebWed 14 FebThu 15 Feb
2TimeSTAFF 1STAFF 2STAFF 3STAFF 4STAFF 5STAFF 6STAFF 1STAFF 2STAFF 3STAFF 4STAFF 5STAFF 6STAFF 1STAFF 2STAFF 3STAFF 4STAFF 5STAFF 6STAFF 1STAFF 2STAFF 3STAFF 4STAFF 5
36.00SITE 1NOTWORKNOTWORK
46.30SITE 1NOTWORKNOTWORK
57.00SITE 1NOTWORKNOTWORK
67.30SITE 1NOTWORKNOTWORK
78.00TRAVELNOTWORKNOTWORK
88.30SITE 2NOTWORKNOTWORK
99.00SITE 2NOTWORKSITE 6
109.30TRAVELNOTWORKSITE 6
1110.00SITE 7NOTWORKSITE 6
1210.30SITE 7NOTWORKTRAVEL
1311.00SITE 7SITE 2SITE 5
1411.30SITE 7SITE 2SITE 5
1512.00SITE 7SITE 2SITE 5
1612.30SITE 7SITE 2SITE 5
1713.00TRAVELTRAVELBREAK
1813.30BREAKBREAKTRAVEL
1914.00PREPSITE 4PREP
2014.30PREPSITE 4PREP
2115.00SITE 5SITE 4PREP
2215.30SITE 5SITE 4PREP
2316.00NOTWORKSITE 4SITE 3
2416.30NOTWORKSITE 4SITE 3
2517.00NOTWORKTRAVELNOTWORK
2617.30NOTWORKTRAVELNOTWORK
2718.00NOTWORKSITE 5NOTWORK
2818.30NOTWORKSITE 5NOTWORK
2919.00NOTWORKSITE 5NOTWORK
3019.30NOTWORKSITE 5NOTWORK
3120.00NOTWORKTRAVELNOTWORK
3220.30NOTWORKSITE 3NOTWORK
3321.00NOTWORKSITE 3NOTWORK
3421.30NOTWORKNOTWORKNOTWORK
Sheet1
 
Upvote 0
Try This
Code:
Public Sub Test1()
Call MyStaff(1)
End Sub
Public Sub Test2()
Call MyStaff(2)
End Sub
Public Sub Test3()
Call MyStaff(6)
End Sub


Public Sub MyStaff(Staff As Long)
Dim WB As Workbook
Dim WS As Worksheet
Dim ShtName As String
Dim StrtCol As Long, Cols As Long
ShtName = "Sheet1"


Set WB = ThisWorkbook
Set WS = WB.Worksheets(ShtName)
StrtCol = 1 ' you start from A =1 B =2
Cols = 6 '  Staff Cont Six
If Staff <= Cols Then
Staff = Staff
Else
Staff = Cols
End If


''Staff = 3
With WS
 .Cells.EntireColumn.Hidden = False
DataRow = 2
LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column
SC = Split(.Cells(1, StrtCol + 1).Address(True, False), "$")(0)
LC = Split(.Cells(1, StrtCol + LastColumn + 1).Address(True, False), "$")(0)
    .Cells.EntireColumn.Hidden = False
    .Columns(SC & ":" & LC).EntireColumn.Hidden = True
For c = 1 To LastColumn
    If (Cols - WorksheetFunction.Ceiling(c, Cols) + c) + Staff = Staff + StrtCol Then
    Adrs = Split(.Cells(1, c + Staff).Address(True, False), "$")(0)
    HiDCol = HiDCol & IIf(HiDCol <> "", ",", "") & Adrs & ":" & Adrs
    End If
Next
 .Range(HiDCol).EntireColumn.Hidden = False
End With


End Sub
 
Upvote 0
Another option, which doesn't need loads of buttons
Code:
Sub HideStaff()

   Dim Ans As String
   Dim hdr As Variant
   Dim Cnt  As Long
   
   Ans = InputBox("Please enter staff member")
   If Ans = "" Then Exit Sub
   hdr = Rows(2).SpecialCells(xlConstants)
   Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").UsedRange.Offset(, 1).EntireColumn.Hidden = True
   For Cnt = 1 To UBound(hdr, 2)
     If InStrRev(hdr(1, Cnt), Ans, , vbTextCompare) Then Columns(Cnt).Hidden = False
   Next Cnt
      
   
End Sub
When you run the macro it will ask you for the name of the staff member, change the sheet name in red if needed
 
Upvote 0
custom Ribbon Tab have:(Pls Download Work Book below link)
6 Tgl Btn you can choose your Staff
main Btn show All
by Fluff code
Pls Clic below Link

WorkBook
 
Upvote 0
Thanks Dossfm0q & Fluff!

I got Fluff's code to work and that's done the job brilliantly, thank you so so much. Dossfm0q thank you also for your code and putting that all in your attached sheet, I unfortunately couldn't get it to work but that's no doubt due to my complete lack of VBA know how :confused:

Your second option did though make me think that being able to choose to show more than one staff at a time could be quite helpful (not sure if that was actually what was included in your code), as well as the current option of showing one staff member at a time.

By using Fluff's code that's already working on my sheet, is there an edit / addition I could add to that so when the macro asks for the staff member name I could type two or more names in (a selection of staff) to show those staff together? - It'd be a 'nice to have' but definitely not an essential :smile:

Thank you again for your help!

Tanya :smile:
 
Upvote 0
Give this a try
Code:
Sub HideStaff()

   Dim Ans As Variant
   Dim Hdr As Variant
   Dim Cnt  As Long
   Dim i As Long
   
   Ans = InputBox("Please enter staff member")
   If Ans = "" Then Exit Sub
   Hdr = Rows(2).SpecialCells(xlConstants)
   Sheets("Sheet1").UsedRange.Offset(, 1).EntireColumn.Hidden = True
   If InStr(Ans, ",") Then
      Ans = Split(Ans, ",")
      For i = 0 To UBound(Ans)
         For Cnt = 1 To UBound(Hdr, 2)
           If InStrRev(Hdr(1, Cnt), trim(Ans(i)), , vbTextCompare) Then Columns(Cnt).Hidden = False
         Next Cnt
      Next i
   Else
      For Cnt = 1 To UBound(Hdr, 2)
        If InStrRev(Hdr(1, Cnt), Ans, , vbTextCompare) Then Columns(Cnt).Hidden = False
      Next Cnt
   End If
   
End Sub
For it to work, when you enter more than one staff member, each person must be separated by a comma
 
Upvote 0
That worked brilliantly, thanks Fluff :) :)

I have also put in place a button to show for selected date range and the code for that is working nicely (amazingly I might actually be learning some coding thanks to your wonderful help!), however it undoes the staff selection (reverting to showing for all staff). Vice versa also for the staff button - if I have already selected a date range then when selecting a staff member via the staff button it undoes the date range selection. Is there a way I can have these two macros play nicely together so they don't undo the selection already made by the other macro?

Thanks again - any thoughts greatly appreciated!

Tanya :)
 
Upvote 0
What code do you have for selecting the date range?
 
Upvote 0

Forum statistics

Threads
1,223,639
Messages
6,173,499
Members
452,517
Latest member
SoerenB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top