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:
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)
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
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
Last edited by a moderator: