Columns(i).Hidden not changing

[Bryan Loeper]

I'm making a toolbar dynamically to hide columns of a workbook. If I
use Columns(i).Hidden = True|False, it works. If I use it in the
below sub (set to the .OnAction of the toolbar button), it doesn't
work correctly, although the sub continues to run past it. The .Tag
nonsense was because it was getting fired twice per button click, so I
added that to keep track of the button's clicked state more
accurately. Any ideas on why my column won't hide?

Sub ButtonToggle()
Dim ctlPressed As CommandBarButton
Dim i As Long
Set ctlPressed = Application.CommandBars.ActionControl
With ctlPressed
i = .index + 1
If .Tag = "0" Then
If .State = msoButtonDown Then
.State = msoButtonUp
Else
.State = msoButtonDown
End If
Columns(i).Hidden = .State
.Tag = "1"
Else
.Tag = "0"
End If
End With
End Sub

 

[Jim Thomlinson]

Not too sure why you wold be having that problem but have you tried using...

Columns(i).Hidden = CBool(.State)

to coerece the state value to a boolean? I can't really test it without more
of your code but you can give it a try...

 

[Bryan Loeper]

Just tried that, no dice. Here's the rest of my code. The above is
in a Module. The following is in ThisWorkbook:

Private Sub Workbook_Open()
ShowHide
End Sub

Sub ShowHide()
Dim cbarName As String
cbarName = "Show/Hide"
Dim cbar As CommandBar
'Delete bar if it exists.
On Error Resume Next
Application.CommandBars(cbarName).Delete
On Error GoTo 0
'Add bar
Set cbar = Application.CommandBars.Add(Name:=cbarName)
Dim i As Long
For i = 2 To 78
Dim Ctrl As CommandBarButton
Set Ctrl = cbar.Controls.Add(msoControlButton)
With Ctrl
.Caption = ActiveSheet.Cells(11, i).Value
.OnAction = "ButtonToggle()"
.Style = msoButtonCaption
.State = Columns(i).EntireColumn.Hidden
.Tag = "0"
End With
Next i
cbar.Visible = True
End Sub

 

[Jim Thomlinson]

You're gonna kick yourself. After all of the very nice code you wrote you
none of it ran because your OnAction is wrong...

..OnAction = "ButtonToggle"
not
..OnAction = "ButtonToggle()"

 

[Bryan Loeper]

:argh:

You're absolutely right. Thanks for the help! It also fixed
that .Tag workaround.