Help Filtering Columns with checklist

[samgeorge87]

Hi,

I'm trying to create a spreadsheet that allows me to filter columns (hide/show) them according to a checklist. It is for use as a line-list pro forma for Music shows. So across Row C I have various instruments listed from C3 to CA3. I want to be able to check these on/off in some way so that only the instruments/lines that I require in any particular show remain visible.

Please help!

Sam

 

[alansidman]

Where is the checklist located? Same sheet, particular column. Different Sheet? Particular column?

 

[mumps]

The simplest way I can think of is to use a Worksheet_BeforeDoubleClick event where you double click any cell in the range C3:CA3 and that column will then be hidden. You double click as many columns as you want. Then you could create a button on your sheet that when clicked, would show all the columns again. If you want to give this a try, copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C3:CA3")) Is Nothing Then Exit Sub
    Target.EntireColumn.Hidden = True
End Sub

Next copy and paste the following macro in a regular module and run it manually by pressing the F5 key or assign it to a button that you can insert on your sheet.

Sub ShowAll()
    ActiveSheet.Columns.Hidden = False
End Sub

 

[MickG]

Another option:-

Place this code in you sheet Module:-
Right Click Sheet Tab, select from Drop down "View Code", Vbwindow appears.
Paste code into VBWindow.
Close VBWindow.

With code Loaded:- Double click "A1", Your List from "C3 to CA3" should now be displayed in Column "A" starting "A3
Select any cell in column "A" (A3 on ) . The cell will turn yellow and the appropriate Column will now be Hidden, Repeat for more columns.
Click any of the yellow cells in column "A" again for the related column to re-appear.

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]Set[/COLOR] Rng = Range("C3", Cells(3, Columns.Count).End(xlToLeft))
    Rng.Copy
    Range("A3").PasteSpecial Transpose:=True
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]If[/COLOR] Target.Column = 1 And Target.Row > 2 And Target <> "" [COLOR=navy]Then[/COLOR]
    Target.Interior.Color = IIf(Target.Interior.Color = vbYellow, xlNone, vbYellow)
    [COLOR=navy]If[/COLOR] Target.Interior.Color = vbYellow [COLOR=navy]Then[/COLOR]
        Columns(Target.Row).Hidden = True
    [COLOR=navy]Else[/COLOR]
        Columns(Target.Row).Hidden = False
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[samgeorge87]

This worked an absolute treat, thank you!

I tried to amend the code to do the same with rows. I amended the range from A4 to A36, and changed Column to Row in the code, but it didn't seem to like it. Any ideas?

The simplest way I can think of is to use a Worksheet_BeforeDoubleClick event where you double click any cell in the range C3:CA3 and that column will then be hidden. You double click as many columns as you want. Then you could create a button on your sheet that when clicked, would show all the columns again. If you want to give this a try, copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C3:CA3")) Is Nothing Then Exit Sub
    Target.EntireColumn.Hidden = True
End Sub

Next copy and paste the following macro in a regular module and run it manually by pressing the F5 key or assign it to a button that you can insert on your sheet.

Sub ShowAll()
    ActiveSheet.Columns.Hidden = False
End Sub

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A4:A36")) Is Nothing Then Exit Sub
    Target.EntireRow.Hidden = True
End Sub

in the worksheet code module and

Sub ShowAll()
    ActiveSheet.Rows.Hidden = False
End Sub

in the regular module.

 

[samgeorge87]

It give me an 'ambiguous name detected' error message...

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A4:A36")) Is Nothing Then Exit Sub
    Target.EntireRow.Hidden = True
End Sub

in the worksheet code module and

Sub ShowAll()
    ActiveSheet.Rows.Hidden = False
End Sub

in the regular module.

 

[mumps]

This most likely means that you have two or more macros with the same name. You have to combine the code into one macro such as follows:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C3:CA3,A4:A36")) Is Nothing Then Exit Sub
    If Target.Row = 3 Then
        Target.EntireColumn.Hidden = True
    End If
    If Target.Column = 1 Then
        Target.EntireRow.Hidden = True
    End If
End Sub

Sub ShowAll()
    Dim response As Long
    response = InputBox("Enter the number 1 to show all columns" & Chr(10) & "or the number 2 to show all rows.")
    If response = 1 Then
        ActiveSheet.Columns.Hidden = False
    ElseIf response = 2 Then
        ActiveSheet.Rows.Hidden = False
    End If
End Sub

 

[samgeorge87]

That works a treat. Brilliant! Thanks so much of your help.

This most likely means that you have two or more macros with the same name. You have to combine the code into one macro such as follows:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C3:CA3,A4:A36")) Is Nothing Then Exit Sub
    If Target.Row = 3 Then
        Target.EntireColumn.Hidden = True
    End If
    If Target.Column = 1 Then
        Target.EntireRow.Hidden = True
    End If
End Sub

Sub ShowAll()
    Dim response As Long
    response = InputBox("Enter the number 1 to show all columns" & Chr(10) & "or the number 2 to show all rows.")
    If response = 1 Then
        ActiveSheet.Columns.Hidden = False
    ElseIf response = 2 Then
        ActiveSheet.Rows.Hidden = False
    End If
End Sub

 

[mumps]

You are very welcome.