1st post

[rjsteen3]

Familiar with coding format but with electronics but zero experience with VBA, so I am struggling with what, I believe, is a simple challenge.
A basic worksheet with names down the first column, programs along the first row, then either a date of completion or n/a for each course per name (see attached image "excel1")
I wish to use a toggle button to quickly hide all data not pertaining to the selected name. I have done so with the following cumbersome code:

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then
    Rows("3:100").Select
    Selection.EntireRow.Hidden = True
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = True
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Columns("Q:Z").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
   
Else
    Columns("A:AA").Select
    Selection.EntireColumn.Hidden = False
    Rows("1:101").Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
   
End If
End Sub

The above works (see attached image "excel2"), but I have to create the routine for each individual name/row with appropriate modifications to rows/columns. There is a glitch as well - if any auto-filtering has been performed (i.e. only "maintenance") has been selected) before toggling, I cannot go back to the original filtered view

What I am hoping for is:

Assign a toggle to each name(row)

When toggle is True
Display rows 1 & 2 (headers)
Display the row of the selected name (i.e. the one with the active toggle button) showing columns 1 & 2 (name/department)
Hide all other active rows (those containing data)
Hide all columns (including headers in rows 1 & 2) that contain "n/a"
When toggle is False
If filter(s) was used:
Return to filtered view
Return curser to A1
Else
Unhide all data
Return curser to A1

I know asking for someone to write the code is in poor taste - but time limitations have me doing so. I am hoping to study/learn the resulting code so that I can grow my skill set in VBA for future challenges. As stated, I can code in C, but I have no experience with VBA declarations/commands, The above code was the best I could hack together this morning and will result in a growing monstrosity with each added row/button
Thanks in advance
Ralph

 

[DanteAmor]

Hi and welcome to MrExcel

I recommend using double click on the cell instead of ToggleButton.
If you have 100 rows, you will have to create 100 ToggleButtons and assign a code to each ToggleButton.

So it would work as follows. If you double-click on cell A1, it would show everything. If you double-click, for example, in cell A6, rows 2 to 5, 7 to 100 and the respective columns are hidden. This way you would only have a code.

Put the following code in the events of your sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim ini As Long, n As Long
  
  If Target.Address(0, 0) = "A1" Then
    Range("A:AA").EntireColumn.Hidden = False
    Rows("1:101").EntireRow.Hidden = False
    Cancel = True
  ElseIf Target.Column = 1 Then
    Range("D:D, F:F, Q:Z").EntireColumn.Hidden = True
    Rows("2:101").EntireRow.Hidden = True
    Target.EntireRow.Hidden = False
    Range("A1").Select
  End If
End Sub

_____________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
_____________________________

 

[Peter_SSs]

Welcome to the MrExcel board!

A few tips for getting the best help in the shortest time.

• Give your thread a meaningful title.
• Use code tags for vba code to preserve indentation and making your code easier to read and better for copy/paste to test.
• Use other tags also to help preserve formatting.

I have edited your post regarding the second two points and my signature block below has links to more information.

 

[rjsteen3]

Hi and welcome to MrExcel

I recommend using double click on the cell instead of ToggleButton.
If you have 100 rows, you will have to create 100 ToggleButtons and assign a code to each ToggleButton.

So it would work as follows. If you double-click on cell A1, it would show everything. If you double-click, for example, in cell A6, rows 2 to 5, 7 to 100 and the respective columns are hidden. This way you would only have a code.

Put the following code in the events of your sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim ini As Long, n As Long
 
  If Target.Address(0, 0) = "A1" Then
    Range("A:AA").EntireColumn.Hidden = False
    Rows("1:101").EntireRow.Hidden = False
    Cancel = True
  ElseIf Target.Column = 1 Then
    Range("D:D, F:F, Q:Z").EntireColumn.Hidden = True
    Rows("2:101").EntireRow.Hidden = True
    Target.EntireRow.Hidden = False
    Range("A1").Select
  End If
End Sub

_____________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
_____________________________

Thank you for your assistance
I believe I understand the first "If" statement - essentially, if A1 is double-clicked, display the entire worksheet

The ElseIf appears to be: if any cell in column 1 other than A1 is double clicked, display the associated row (and the Header row1) while hiding all other rows

My challenge is the first component of the ElseIF statement "Range("D:D, F:F, Q:Z").EntireColumn.Hidden = True:. This statement works perfect for row 2, but hides desired data for all other rows. Is there a declaration which searches for "n/a" within the double-clicked row and hides the column containing the "n/a"?

Thanks again

 

[DanteAmor]

but hides desired data for all other rows. Is there a declaration which searches for "n/a" within the double-clicked row and hides the column containing the "n/a"?

Besides hiding these Range columns ("D: D, F: F, Q: Z"), you also want to hide the ones with "n/a" or you just want to hide the ones with "n/a"?

 

[rjsteen3]

Besides hiding these Range columns ("D: D, F: F, Q: Z"), you also want to hide the ones with "n/a" or you just want to hide the ones with "n/a"?

Just the columns with "n/a"
The range above only works with row A2

 

[rjsteen3]

Perhaps a simpler statement:
If any cell in column 1 (aside from A1) is double clicked:
1 -Always display row 1 (header)
2 - hide all rows except the one double-clicked
3 - hide all columns within the double-clicked row with "n/a"

 

[DanteAmor]

Just the columns with "n/a"

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim ini As Long, n As Long, j As Long
  
  If Target.Address(0, 0) = "A1" Then
    Range("A:AA").EntireColumn.Hidden = False
    Rows("1:101").EntireRow.Hidden = False
    Cancel = True
  ElseIf Target.Column = 1 Then
    For j = 2 To Cells(1, Columns.Count).End(1).Column
      If Cells(Target.Row, j).Value = "n/a" Then
        Columns(j).EntireColumn.Hidden = True
      End If
    Next
    Rows("2:101").EntireRow.Hidden = True
    Target.EntireRow.Hidden = False
    Range("A1").Select
  End If
End Sub

 

[rjsteen3]

Thank you - this is exactly what I was hoping to achieve!

May I trouble you for context:

"If Cells(Target.Row, j).Value = "n/a" Then
Columns(j).EntireColumn.Hidden = True
End If"
Does this work as an incremental counter? (i.e. enter loop. look for n/a in row 1, hide column, increment counter +1, back to top, look for n/a in row 2....etc). Trying to understand the code personal learning

Also: what is the purpose of "Range("A1").Select"? I've run the code with and without - both seem to function -a gain, just for my learning

I greatly appreciate your help. Way back when I was learning C, it was textbook only. Forums like this and people such as yourself greatly speed the educational curve

 

[DanteAmor]

Does this work as an incremental counter?

that's right, the variable 'j' is incremented one by one, it represents the column number.

Range("A1").Select

It was in your initial requirement .
But it also helps to leave the cell with double click, since when you double click on a cell, you enter to edit the cell.

Way back when I was learning C, it was textbook only

The same thing happened to me, when I learned to program there was no internet .