Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel

[JenW]

Hello,
I'm using Microsoft Office 365, and I actually need three buttons, but if you guys can help me with the first one, I hope to be able to do the others on my own. I’m trying to learn, but of course, am currently clueless. I’m trying to use a VBA toggle button titled, “Show/Hide All Y/N Statements” to show/hide several adjacent and non-adjacent columns:

My first attempt I tried:



Dim xAddress As String

xAddress = “D:E,N:0,X:Y,AH:AI,AR:AS,BB:BC,BL:BM,BV:BW,CF:CG,CP:CQ,CZ:DA,DJ:DK,DT:DU,ED:EE,EN:EO,EX:EY,FH:FI,FR:FS,I:J,S:T,AC:AD,AM:AN,AW:AX,BG:BH,BQ:BR,CA:CB,CK:CL,CU:CV,DE:DF,DO:DP,DY:DZ,EI:EJ,ES:ET,FC:FD,FM:FN,FW:FX"

If ToggleButton1.Value Then

Application.ActiveSheet.Columns(xAddress).Hidden = True

Else

Application.ActiveSheet.Columns(xAddress).Hidden = False

End If

End Sub



On my second attempt, tried the same thing with:



Application.ActiveSheet.Columns(xAddress)EntireColumn.Hidden = True

Else

Application.ActiveSheet.Columns(xAddress)EntireColumn.Hidden = False

End If

End Sub



On my third attempt I tried:



Private Sub ToggleButton1_Click()

Range("D:D,E:E,N:N,O:O,X:X,Y:Y,AH:AH,AI:AI,AR:AR,AS:AS,BB:BB,BC:BC,BL:BL,BM:BM,BV:BV,BW:BW,CF:CF,CG:CG,CP:CP,CQ:CQ,CZ:CZ,DA:DA,DJ:DJ,DK:DK,DT:DT,DU:DU,ED:ED,EE:EE,EN:EN,EO:EO,EX:EX,EY:EY,FH:FH,FI:FI,FR:FR:FS:FS,I:I,J:J,S:S,T:T,AC:AC,AD:AD,AM:AM,AN:AN,AW:AW,AX:AX,BG:BG,BH:BH,BQ:BQ,BR:BR,CA:CA,CB:CB,CK:CK,CL:CL,CU:CU,CV:CV,DE:DE,DF:DF,DO:DO,DP:DP,DY:DY,DZ:DZ,EI:EI,EJ:EJ,ES:ES,ET:ET,FC:FC,FD:FD,FM:FM,FN:FN,FW:FW,FX:FX").EntireColumn.Hidden = Not Range("D:D,E:E,N:N,O:O,X:X,Y:Y,AH:AH,AI:AI,AR:AR,AS:AS,BB:BB,BC:BC,BL:BL,BM:BM,BV:BV,BW:BW,CF:CF,CG:CG,CP:CP,CQ:CQ,CZ:CZ,DA:DA,DJ:DJ,DK:DK,DT:DT,DU:DU,ED:ED,EE:EE,EN:EN,EO:EO,EX:EX,EY:EY,FH:FH,FI:FI,FR:FR:FS:FS,I:I,J:J,S:S,T:T,AC:AC,AD:AD,AM:AM,AN:AN,AW:AW,AX:AX,BG:BG,BH:BH,BQ:BQ,BR:BR,CA:CA,CB:CB,CK:CK,CL:CL,CU:CU,CV:CV,DE:DE,DF:DF,DO:DO,DP:DP,DY:DY,DZ:DZ,EI:EI,EJ:EJ,ES:ES,ET:ET,FC:FC,FD:FD,FM:FM,FN:FN,FW:FW,FX:FX").EntireColumn.Hidden

End Sub



I would like to know the best way to create this button, but I would also like to understand the difference between the above attempts and how/when to use them properly.

Thanks,
Jen W

 

[dmt32]

Hi,
welcome to forum

Try this update to your code & see if it does what you want

Private Sub ToggleButton1_Click()
    Dim rng As Range
   
     Set rng = Range("D:E,N:O,X:Y,AH:AI,AR:AS,BB:BC,BL:BM,BV:BW,CF:CG,CP:CQ,CZ:DA,DJ:DK,DT:DU,ED:EE,EN:EO,EX:EY,FH:FI,FR:FS," & _
                      "I:J,S:T,AC:AD,AM:AN,AW:AX,BG:BH,BQ:BR,CA:CB,CK:CL,CU:CV,DE:DF,DO:DP,DY:DZ,EI:EJ,ES:ET,FC:FD,FM:FN,FW:FX")
   
     With Me.ToggleButton1
     rng.EntireColumn.Hidden = .Value
        .Caption = IIf(.Value, "Show", "Hide")
      End With

End Sub

Dave

 

[JenW]

Hi,
welcome to forum

Try this update to your code & see if it does what you want

Private Sub ToggleButton1_Click()
    Dim rng As Range
  
     Set rng = Range("D:E,N:O,X:Y,AH:AI,AR:AS,BB:BC,BL:BM,BV:BW,CF:CG,CP:CQ,CZ:DA,DJ:DK,DT:DU,ED:EE,EN:EO,EX:EY,FH:FI,FR:FS," & _
                      "I:J,S:T,AC:AD,AM:AN,AW:AX,BG:BH,BQ:BR,CA:CB,CK:CL,CU:CV,DE:DF,DO:DP,DY:DZ,EI:EJ,ES:ET,FC:FD,FM:FN,FW:FX")
  
     With Me.ToggleButton1
     rng.EntireColumn.Hidden = .Value
        .Caption = IIf(.Value, "Show", "Hide")
      End With

End Sub

Dave

 

[JenW]

Thank you Dave! That worked brilliantly, and I was able to create all of the buttons I needed!

 

[dmt32]

Thank you Dave! That worked brilliantly, and I was able to create all of the buttons I needed!

most welcome & appreciate your feedback

Dave