Hide Multiple Columns by Command Button

Hi everyone,

Can anyone help me with this problem

I have a monitoring file for reinforcing bars (please see attached file)

I am monitoring the request, delivery and balances. what i want to achieve is to make an active control button that hides columns

for instance, there are multiple request, so instead of scrolling to the right, i will just click the buttons so that my views are compressed.

i'd be glad if someone can help.

VBA needed.xlsm

Here you go, I did the code for "Hide Weight" button, it should be fairly straightforward how to adjust this for your other buttons. You'll need one Sub per button.

Please note that the name of the sub ( the "Hide_Weight" part), needs to match the name of your button (Developer Mode > Right Click .Properties > Name field)

Private Sub Hide_Weight_Click()

If Hide_Weight.Caption = "Hide Weight" Then
    With Rows(6)
        Set c = .Find("weight")
        If Not c Is Nothing Then
            FirstAdd = c.Address
            Do
                Columns(c.Column).EntireColumn.Hidden = True
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAdd
        End If
    End With
    Hide_Weight.Caption = "Unhide Weight"
    
ElseIf Hide_Weight.Caption = "Unhide Weight" Then
    With Rows(6)
        Set c = .Find("weight")
        If Not c Is Nothing Then
            FirstAdd = c.Address
            Do
                Columns(c.Column).EntireColumn.Hidden = False
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAdd
        End If
    End With
    Hide_Weight.Caption = "Hide Weight"
End If

End Sub

that was fast

Thank you soo much @walruseggman

just one last thing.

I will add a dropdown menu that will show the request number

VBA needed (updated).xlsm

I was surprised that VBA won't allow hiding a range of columns by column number. That is, I couldn't get Columns("6:" c.Column -1) to work. Oh well, this code should work fine.

Again, make sure your button name matches the Sub name.

Private Sub Show_Request_Click()

Columns.EntireColumn.Hidden = False

With Rows(7)
Set c = .Find(CStr(Range("D2").Value))
If Not c Is Nothing And Range("D2").Value <> "1" Then
    Columns("F:" & Split(c.Offset(0, -1).Address, "$")(1)).EntireColumn.Hidden = True
End If
End With

End Sub

walruseggman,

is there a way that only the chosen item on drop down list will be shown?

because when i chose 0001, items 0001, 0002 and 0003 are shown, but when i chose 0002, only item 0001 is hidden..

is it possible to show only 0002 when i chose 0002 and not 0002 and everything that follows?

I recommend adding an "All" value to your dropdown so that you can show all the requests again after showing just one. I've accounted for that in the code.

Private Sub Show_Request_Click()

If Range("D2").Value = "All" Then
    Columns.EntireColumn.Hidden = False
Else
Columns("F:" & Split(Cells(8, Columns.Count).End(xlToLeft).Address, "$")(1)).EntireColumn.Hidden = True

With Rows(7)
Set c = .Find(CStr(Range("D2").Value))
If Not c Is Nothing Then
    Columns(Split(c.Address, "$")(1) & ":" & Split(c.Offset(0, 5).Address, "$")(1)).EntireColumn.Hidden = False
End If
End With
End If
End Sub

one again @walruseggman, thank you sooo much..

that's cool

You're welcome! Don't forget to mark this thread as solved, and any reputation points (the * in the lower left of my posts) is appreciated.