Questions

Using Visual Basic in Excel to hide columns

Tags:
+
0 Votes
Locked

Using Visual Basic in Excel to hide columns

Hi there,

I am having a bad day...
I am trying to hide columns on a worksheet ("Fixtures") by clicking a checkbox on "Data" sheet. But it doesn't work! Seems fairly simple... but nothing happens when I check the box and I am stuck!

I have the code:-

Private Sub CheckBox1_Click()

Call sheetsunprotect: ' goto subroutine to unprotect all sheets and disable updating

' box checked
If CheckBox1.Value = True Then
' Code to hide data.
Worksheets("Fixtures").Select
Columns("R:T").Hidden = True
End If

'box unchecked
If CheckBox1.Value = False Then
' Code to unhide data.
Worksheets("Fixtures").Select
Columns("R:T").Hidden = False
End If

Call sheetsprotect: ' goto subroutine to protect all sheets and disable updating

' go back to "Data" sheet
Worksheets("Data").Select

End Sub

Can anyone help?

Allan
  • +
    0 Votes

    Seems like I had to use a With command instead....

    Private Sub CheckBox1_Click()

    Call sheetsunprotect: ' goto subroutine to unprotect all sheets and disable updating

    ' box checked
    If CheckBox1.Value = True Then
    ' Code to hide "For", "Against" and "Difference" data.
    Worksheets("Fixtures").Select
    With ActiveSheet
    .Range("R1,T1").EntireColumn.Hidden = True
    End With
    End If

    'box unchecked
    If CheckBox1.Value = False Then
    ' Code to unhide "For", "Against" and "Difference" data.
    Worksheets("Fixtures").Select
    With ActiveSheet
    .Range("R1,T1").EntireColumn.Hidden = False
    End With
    End If

    Call sheetsprotect: ' goto subroutine to protect all sheets and enable updating

    ' go back to "Data" sheet
    Worksheets("Data").Select

    End Sub

    Allan

    +
    0 Votes
    tarunpatkar

    Thanks alot sir, its working.

  • +
    0 Votes

    Seems like I had to use a With command instead....

    Private Sub CheckBox1_Click()

    Call sheetsunprotect: ' goto subroutine to unprotect all sheets and disable updating

    ' box checked
    If CheckBox1.Value = True Then
    ' Code to hide "For", "Against" and "Difference" data.
    Worksheets("Fixtures").Select
    With ActiveSheet
    .Range("R1,T1").EntireColumn.Hidden = True
    End With
    End If

    'box unchecked
    If CheckBox1.Value = False Then
    ' Code to unhide "For", "Against" and "Difference" data.
    Worksheets("Fixtures").Select
    With ActiveSheet
    .Range("R1,T1").EntireColumn.Hidden = False
    End With
    End If

    Call sheetsprotect: ' goto subroutine to protect all sheets and enable updating

    ' go back to "Data" sheet
    Worksheets("Data").Select

    End Sub

    Allan

    +
    0 Votes
    tarunpatkar

    Thanks alot sir, its working.