Hide/Unhide columns

[Vincent88]

Hi Guys, Need advice to modify the code to use Cell B1 as toggle button to hide and unhide the columns.
Right now, when click B1, it hides the columns but I have to move cursor to other cell & click once to open columns.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

  Select Case Sh.Name
    Case "Data"
      Exit Sub
    Case Else
  End Select

  If g_blnWbkShtSelChange Then Exit Sub
  If Selection.Count = 1 Then
    If Not Intersect(Target, Range("B1")) Is Nothing Then
      g_blnWbkShtSelChange = True
        Columns("C:G").EntireColumn.Hidden = True
    Else
        Columns("C:G").EntireColumn.Hidden = False
   End If
   End If

End Sub

 

[rlv01]

You talk about 'clicking' on B1, but you are using the SelectionChange event. Selection is different. So while this will work

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
Select Case Sh.Name
        Case "Data"
            Exit Sub
        Case Else
    End Select

    If Target.Address = "$B$1" Then
        If Columns("C:G").EntireColumn.Hidden Then
            Columns("C:G").EntireColumn.Hidden = False
        Else
            Columns("C:G").EntireColumn.Hidden = True
        End If
    End If
End Sub

It requires that the cell selection change to toggle, which means you have to move the selection on and off B1. In other words, you cannot just stay on Cell B1 and click. To do that, you are better off using the SheetBeforeDoubleClick event:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    Select Case Sh.Name
        Case "Data"
            Exit Sub
        Case Else
    End Select

    If Target.Address = "$B$1" Then
        If Columns("C:G").EntireColumn.Hidden Then
            Columns("C:G").EntireColumn.Hidden = False
        Else
            Columns("C:G").EntireColumn.Hidden = True
        End If
    End If
    Application.SendKeys ("{ESC}")
End Sub

 

[EXCEL MAX]

Hello Vincent88,
I don't know any way to do that, seems imposible.
Also don't know how far you can to go out of basic question,
but you can try to use this the trick. Use difference between active and selected cell.
This code selects two dislocated cells, but "B1" is stays active cell.

Insert this to the standard module. This is switching procedure.

Public g_blnWbkShtSelChange As Boolean

Sub ToggleButtonSwitch()

    If g_blnWbkShtSelChange = False Then
       g_blnWbkShtSelChange = True
    Else
       g_blnWbkShtSelChange = False
    End If
   
End Sub

Now insert this in the Workbook_SheetSelectionChange event.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Selection.Address = "$B$1" Then Exit Sub

    Call ToggleButtonSwitch
   
    If g_blnWbkShtSelChange Then
        Union([B1], [Z1]).Select
        [B1].Activate
        Columns("C:G").EntireColumn.Hidden = True
    Else
        Union([B1], [Z1]).Select
        [B1].Activate
        Columns("C:G").EntireColumn.Hidden = False
    End If

End Sub

If the range [Z1] is in use, you can replace him with some unuseble range.
Hope so this is a litle bit closer to the answer.

 

[Vincent88]

Hi riv01 and EXCEL MAX, all your codes work well. Thanks a lot

 

[EXCEL MAX]

We are glad that you are satisfied and that you finally found a solution.