I have this code on a command button. and once the columns are hidden, I want to unhide it by clicking the same button

Private Sub CommandButton16_Click()
    For i = 22 To 145
    
    If Worksheets("Material Masterlist").Cells(3, i).Value = "Quantity" Then
    Worksheets("Material Masterlist").Columns(i).Hidden = True
    CommandButton16.Caption = "Unhide Quantity"
        CommandButton15.Font.Size = 7
    End If
    Next
End Sub

CodePudding user response：

Replace:

Worksheets("Material Masterlist").Columns(i).Hidden = True

With:

Worksheets("Material Masterlist").Columns(i).Hidden = (Not Worksheets("Material Masterlist").Columns(i).Hidden)

CodePudding user response：

It seems that the requirement is to hide or unhide the columns with value Quantity base on the caption of the CommandButton16, not based on whether the column is already hidden or not.

This is my understanding of the requirements:

1. If CommandButton16 caption = "Hide Quantity" (or <> "Hide Quantity", change as required)
   1.1. Hide column of any cell in Range [V3:EO3] that is equal to "Quantity"
   If there was at least one cell in Range [V3:EO3] equal to "Quantity" then:
   1.2. Change CommandButton16 caption to "Unhide Quantity"
   1.3. Change CommandButton15 font size to 7

2. If CommandButton16 caption = "Unhide Quantity"
   2.1. Unhide all columns in Range [V3:EO3](i)
   2.2. Change CommandButton16 caption to "Hide Quantity" (change as required)
   2.3. Change CommandButton15 font size to 12 (change as required)

(i) Assumes that only the columns of cells equal to "Quantity" in Range [V3:EO3] are hidden.
*Otherwise: * 2.1. Unhide column of any cell in Range [V3:EO3] that is equal to "Quantity"

Try this code:

Private Sub CommandButton16_Click()
Const kValue As String = "Quantity"     'Use Constants to provide flexibility
Const kHide As String = "Hide "
Const kUnhide As String = "Unhide "
Dim Rng As Range, Cll As Range
Dim blHide As Boolean
    
    Set Rng = Worksheets("Material Masterlist").Cells(3, 22).Resize(1, 124) '1  145 -22     'Set range of cells to be checked

    With CommandButton16
        
        If .Caption = kHide & kValue Then
            
            For Each Cll In Rng.Cells
                With Cll
                    If .Value = kValue Then
                        .Columns.Hidden = True
                        blHide = True
                    End If
                End With
            Next
            
            If blHide Then
                .Caption = kUnhide & kValue
                CommandButton15.Font.Size = 7
            End If
            
        ElseIf .Caption = kUnhide & kValue Then
            
            Rem Use this line if only the columns of cells equal to "Quantity" in Range [V3:EO3] are hidden.
                Rng.Columns.Hidden = False
            
            Rem Otherwise use these lines
                For Each Cll In Rng.Cells
                    If Cll.Value = kValue Then Cll.Columns.Hidden = False
                Next
                        
            .Caption = "Hide Quantity"
            CommandButton15.Font.Size = 12   'Change as required
            
        End If
    End With
    
    End Sub

Resource used: Worksheet.Range, With statement