Using Checkboxes to Hide Individual Rows, One Atta Time, with VBA

[atypicalguy]

Hey All!

First post on this board, really impressed with and appreciate this community!

I'm coming in with near to 0 knowledge of VBA, so any and all explanations/ideas/troubleshoots are appreciated.

I've got a task list that I'm wanting to mark completions with a ticked checkbox, and once the checkbox is ticked, it hides it's row.

I've been browsing this board and have found some macros for hiding multiple rows via a single checkbox, but am having trouble coming up with a macro for individual rows with their own checkbox.

Any and all help is appreciated!

 

[Jeffrey Mahoney]

Please try this. I like using Wingding characters instead of real checkboxes

I created a named range using this formula to make it dynamic and called it CheckBoxes:
=OFFSET(Sheet1!$A$1,1,0,MAX((Sheet1!$A:$A<>"")*(ROW(Sheet1!$A:$A))-ROW(Sheet1!$A$1),1))

Book4
	A	B	C	D
1	CB	One	Two	Three
2	¨	1	2	3
3	þ	4	5	6
Sheet1

Put this code in the sheet module instead of a standard module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("Checkboxes")) Is Nothing Then
    Cancel = True
    Application.EnableEvents = False
    
    If Target.Value = "¨" Then
      Target.Value = "þ"
      Target.EntireRow.Hidden = True
    Else
      Target.Value = "¨"
    End If
    Application.EnableEvents = True
  End If
  
End Sub

 

[atypicalguy]

Jeffrey! Thanks for the response.

I should've been more specific with my initial request.

I've already used a filter function to aggregate and sort the most important tasks based on date:

=FILTER(Table167[#All],((Table167[[#All],[Next QAT Due On]]<B21+30)))

Instead of rebuilding the sheet from scratch with the formula you provided, is there an easier way to go about hiding the completed tasks other than a checkmark?

I'd like to continue down the VBA route unless there's an easier way.

 

[Fluff]

Why not just add an extra column to the table & put an X in it? That way you can include that in your formula so it doesn't show completed tasks.

 

[atypicalguy]

@Fluff , good idea! I tried this out, but I couldn't figure out how to link the formula results with the additional column input

 

[Fluff]

What is the name of the column?

 

[atypicalguy]

"Scheduled?"
- Yes/No

 

[Fluff]

Ok, how about

=FILTER(Table167,(Table167[Next QAT Due On]<B21+30)*(Table167[Scheduled?]="no"))

 

[atypicalguy]

This is spitting back a "CALC!" error.

The part my brain can't wrap around: I'm using the filter formula to bring the overdue tasks to the top of the page and sorting them by date, but if I enter "yes/no" into the filter'd "Scheduled?" column, it won't stick to the corresponding table/souce column, will it?

 

[Fluff]

You need to add that to the source table.