Toggle hide columns

[spacecaptainsuperguy]

This has been addressed by a number of other posts but not exactly the way I'm looking to do it, so I'm hopeful y'all can help. I have a large data table that spans columns A:AM that a number of people use. For person #1 they may only want to see a handful of columns and will hide the ones that don't pertain to them and for person #2 it would be a different set of columns and for person #3 yet another set of columns. What I'd like to do is create a macro button for each of them that hides only the columns they want hidden from view. (That part is easy enough) Problem is that it is typical for someone to hide columns, make their changes and then save and close with columns still hidden. Whoever gets into it next has to manually unhide all the columns and hide the ones they want hidden. I use all of the fields so I'm constantly unhiding columns. I was hoping to make a code for everyone that toggled between hiding their particular columns and then unhiding all hidden columns. I would customize the macro for each person to hide their particular columns but it would universally unhide all. I just can figure out how to get it to toggle between those two. To put it in simple terms, button press 1: unhide all columns and just hide the ones I want hidden, button press 2: unhide all columns.

Any idea are much appreciated.

 

[Micron]

What I'd like to do is create a macro button for each of them that hides only the columns they want hidden from view. (That part is easy enough)

If that is easy then the rest should be? I would simply unhide all, then next line in the code hides those that need to be hidden. I would not create a button for each of three persons then have basically the same code three times. If there are several columns involved I'd put their preferences in a sheet somewhere, create an array of column numbers from that and use the array as their custom choices within the single sub. If the selections are dynamic and not too numerous I might use a comma separated list from an input box.

 

[SueKi]

Create an unhide all columns macro that triggers on file open?

 

[Micron]

Create an unhide all columns macro that triggers on file open?

Exactly what I was thinking and put together while waiting for OP responses. This assumes a sheet contains the application username in a cell, and next to that, csv values for the rows to hide. In this case, those cells are on the same sheet, so you'd want to include those in the columns to hide, or hide them on another sheet.

Private Sub Workbook_Open()
Dim aryCols() As String, strUser As String, strCols As String
Dim rng As Range
Dim ws As Worksheet
Dim i As Integer

'there could be an option to just unhide and not hide??
strUser = Environ("Username")
Set ws = Sheets("1")
Set rng = ws.Cells.Find(strUser, searchorder:=xlByColumns, SearchDirection:=xlNext)
If Not rng Is Nothing Then
     ws.Columns.EntireColumn.Hidden = False
     strCols = rng.Value
     aryCols() = Split(rng.Offset(0, 1).Value, ",")
     For i = 0 To UBound(aryCols) - 1
          ws.Columns(CLng(aryCols(i))).Hidden = True
     Next
End If

End Sub

 

[Micron]

Here's an adaptation that selects the topmost cell of the first visible column. If active cell becomes hidden, the active cell border looks sort of strange. It also hides columns N:O where I put the username and column selection values.

Private Sub Workbook_Open()
Dim aryCols() As String, strUser As String, strCols As String
Dim rng As Range
Dim ws As Worksheet
Dim i As Long

'there could be an option to just unhide and not hide??
strUser = Environ("Username")
Set ws = Sheets("1")
Set rng = ws.Cells.Find(strUser, searchorder:=xlByColumns, SearchDirection:=xlNext)
If Not rng Is Nothing Then
     ws.Columns.EntireColumn.Hidden = False
     strCols = rng.Value
     aryCols() = Split(rng.Offset(0, 1).Value, ",")
''     For i = 0 To UBound(aryCols) - 1
''          ws.Columns(CLng(aryCols(i))).Hidden = True
''     Next
'adaptation to hide columns containing username and colum values (N and O) on same sheet
     For i = 0 To UBound(aryCols)
          ws.Columns(CLng(aryCols(i))).Hidden = True
     Next
     ws.Columns("N:O").Hidden = True
     i = 1
     Do Until ws.Columns(i).Hidden = False
          ws.Columns(i).Select
          i = i + 1
     Loop
     ws.Cells(1, i).Select
End If

End Sub

 

[spacecaptainsuperguy]

Micron & SueKi,
Thank y'all for your replies. I hadn't thought about creating an unhide on file open, so that may be a viable option. For the sake of discussion, here is what I have been using for one of them, which is just one line of code.

Sub togglebutton_hide()

    Range("F:F,G:G,J:J,N:N,P:P,T:T").EntireColumn.Hidden = Not Range("F:F,G:G,J:J,N:N,P:P,T:T").EntireColumn.Hidden
End Sub

The only negative, if you can call it that, is I don't really want to change the file format from an .xlsx to an .xlsm to house the unhide at open macro. Long story short...some people don't like change. :-/

Another strategy that might work is along the lines of the unhide at open approach. Wouldn't it be possible to write and if then statement that checks to see if any columns are hidden and then unhides them and then alternatively if none are hidden, hide these specific columns?

Thanks again for your suggestions.

 

[spacecaptainsuperguy]

Thank you both for your suggestions....it led me to the right train of thought to figure it out. Here is what I came up with. If there is a better suggestion, please feel free to let me know.

Sub Toggle_Hide_Unhide()

Dim Counter As Integer

Counter = 0
        
        For Each Cell In ActiveWorkbook.ActiveSheet.Rows("1").Cells
           If Cell.EntireColumn.Hidden = True Then
                Counter = Counter + 1
                Cell.EntireColumn.Hidden = False
           End If
        Next Cell

        If Counter = 0 Then
            Range("F:F,G:G,J:J,N:N,P:P,T:T").EntireColumn.Hidden = True
        End If
        
End Sub

 

[Micron]

I don't get that at all. You're looping over 16 thousand plus columns?
Also, I thought each person had a different need, and that looks like it's hard coded for one person.
Also, if there were hidden columns (because someone saved wb with hidden columns), then your hiding code lines won't even execute because Counter will be greater than zero.
I'm no Excel expert so I don't see your point about xlsm, because AFAIK, you can't even run what you posted if the file is an xls or xlsx.

I don't think I have anything else to add for those reasons.

 

[lilgto64]

If you cannot change the file itself to an xlsm, then perhaps you could put your code into each users Personal Macro Workbook as an xlsb.

Or, put a small custom file into each users Auto Start Folder - and have the workbook hidden - this way it opens every time they launch Excel but they never actually see it. I do this with some code which I then set keyboard shortcuts to trigger. I'd have to think about it a bit to see if there is a way for that to detect when another workbook is opened.

The code itself can use Application.UserName to test to see who is using it - and then be set to show or hide each column, not sure if an array could do that. I know I have set the value of a range of cells using an array - would have to see if setting other properties of a range can be done via an array.

 

[lilgto64]

Okay - no Array on the right side of the hidden but can change the array on the left side as you have already done.

Sub ShowOrHide()

Range("F:F,G:G,J:J,N:N,P:P,T:T").EntireColumn.Hidden = False ' unhide everything

' User = Application.UserName ' uncomment this for live
User = InputBox("Enter UserName", "Type A, B lor C") ' use this for testing

If User = "A" Then ' replace "A" with the first user's name

Range("F:F,N:N,P:P").EntireColumn.Hidden = True ' change this to whatever user 1's prefs are

ElseIf User = "B" Then ' replace "B" with the second user's name

Range("G:G,J:J,N:N,T:T").EntireColumn.Hidden = True ' change this to whatever user 2's prefs are

ElseIf User = "C" Then ' replace "C" with the third user's name

Range("N:N,T:T").EntireColumn.Hidden = True ' change this to whatever user 3's prefs are

Else

MsgBox ("Invalid User"), vbCritical ' would not need this or could change to something along the lines of "Your preferences have not been setup..."

End If

End Sub


If many users - perhaps it would better to define an array of Hidden True or False for each user - then use that to walk through the columns and set each to true or false based on the array for the current user.

To trigger it - add a button to the xlsx that points to the macro in the hidden workbook. This can get a bit tricky depending on Mac vs Windows and whether the users typically have their docs stored on a shared drive or not. So might need to add some error checking in there to avoid the user getting dumped into debug mode if they are not configured properly.

Of course if you were going to use the Personal or create a hidden file - then you could have a macro to "Remember Current Settings" and another "Recall"

Not sure on an auto trigger for this when opened a book that doesn't contain the macro - there are application level events - but I would have to do some digging into that - there are some other post in this forum which cover that.