Excel automatically hiding certain columns

[TanyaD]

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

 

[Joe4]

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

 

[TanyaD]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		Mon 12 Feb						Tue 13 Feb						Wed 14 Feb						Thu 15 Feb
2	Time	STAFF 1	STAFF 2	STAFF 3	STAFF 4	STAFF 5	STAFF 6	STAFF 1	STAFF 2	STAFF 3	STAFF 4	STAFF 5	STAFF 6	STAFF 1	STAFF 2	STAFF 3	STAFF 4	STAFF 5	STAFF 6	STAFF 1	STAFF 2	STAFF 3	STAFF 4	STAFF 5
3	6.00	SITE 1	NOTWORK	NOTWORK
4	6.30	SITE 1	NOTWORK	NOTWORK
5	7.00	SITE 1	NOTWORK	NOTWORK
6	7.30	SITE 1	NOTWORK	NOTWORK
7	8.00	TRAVEL	NOTWORK	NOTWORK
8	8.30	SITE 2	NOTWORK	NOTWORK
9	9.00	SITE 2	NOTWORK	SITE 6
10	9.30	TRAVEL	NOTWORK	SITE 6
11	10.00	SITE 7	NOTWORK	SITE 6
12	10.30	SITE 7	NOTWORK	TRAVEL
13	11.00	SITE 7	SITE 2	SITE 5
14	11.30	SITE 7	SITE 2	SITE 5
15	12.00	SITE 7	SITE 2	SITE 5
16	12.30	SITE 7	SITE 2	SITE 5
17	13.00	TRAVEL	TRAVEL	BREAK
18	13.30	BREAK	BREAK	TRAVEL
19	14.00	PREP	SITE 4	PREP
20	14.30	PREP	SITE 4	PREP
21	15.00	SITE 5	SITE 4	PREP
22	15.30	SITE 5	SITE 4	PREP
23	16.00	NOTWORK	SITE 4	SITE 3
24	16.30	NOTWORK	SITE 4	SITE 3
25	17.00	NOTWORK	TRAVEL	NOTWORK
26	17.30	NOTWORK	TRAVEL	NOTWORK
27	18.00	NOTWORK	SITE 5	NOTWORK
28	18.30	NOTWORK	SITE 5	NOTWORK
29	19.00	NOTWORK	SITE 5	NOTWORK
30	19.30	NOTWORK	SITE 5	NOTWORK
31	20.00	NOTWORK	TRAVEL	NOTWORK
32	20.30	NOTWORK	SITE 3	NOTWORK
33	21.00	NOTWORK	SITE 3	NOTWORK
34	21.30	NOTWORK	NOTWORK	NOTWORK
Sheet1

 

[Dossfm0q]

Try This

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

 

[Fluff]

Another option, which doesn't need loads of buttons

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

 

[Dossfm0q]

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

 

[TanyaD]

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

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

Thank you again for your help!

Tanya

 

[Fluff]

Give this a try

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

 

[TanyaD]

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

 

[Fluff]

What code do you have for selecting the date range?