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:

rjsteen3

New Member
Joined
Jul 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
One glitch - same as when I was trying this with a toggle button.
If an Auto-filter has been used before running the script, the sheet does not return to the previous view. Example - if "Maintenance" is selected, 7 names result. If I double-click on a name, the script produces the desired outcome (all n/a columns and all non-selected rows are hidden).
However, when A1 is double-clicked ("script cancel"), the 7 auto-filtered names do not return - only the row itself. The auto-filter needs to be cycled on/off to get back to the group of 7 names
Can this be overcome?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,147,821
Messages
5,743,396
Members
423,792
Latest member
travisds

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