1st post

rjsteen3

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA 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
 

Attachments

  • excel1.png
    excel1.png
    240.3 KB · Views: 6
  • excel2.png
    excel2.png
    170.7 KB · Views: 5
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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:

VBA Code:
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.
_____________________________
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,243
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
Just the columns with "n/a"
Try this:

VBA Code:
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
 
Solution

rjsteen3

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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

1627319978494.png


It was in your initial requirement :giggle:.
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 :LOL:.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,314
Members
425,272
Latest member
Umba

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
Top