How to create a toggle button on the menu? like the Bold, Underline...
button.

Hi OKLover,

"OKLover" wrote in message
...
How to create a toggle button on the menu? like the Bold, Underline...
button.

Add a toolbar button and then assign toggle code to the button.

For example, the following code toggles the visible state of columns A:D on
the active sheet:

Sub ToggleColumnHide()
Dim rng As Range

Set rng = ActiveSheet.Columns("A:D")

rng.Hidden = Not rng.Hidden

End Sub


---
Regards,
Norman

Norman, thanks for your answer. My question is that i know how to create a
menu item as below:


With CommandBars.Add(Name:="MyCheckButton", Position:=msoBarPopup)
With .Controls.Add(Type:=????????)
.OnAction = "someprocedure"
.Caption = "TheMenuItemName"
End With
End With

But it could not be toggled or checked. What's the control type i should
assign to it?

Try the following code. It will add an item to the Tools menu.

Sub AddControl()

Dim Ctrl As Office.CommandBarButton
Set Ctrl = Application.CommandBars.FindControl(ID:=30007) _
.Controls.Add(temporary:=True)
With Ctrl
.Caption = "Click Me"
.State = msoButtonUp
.Visible = True
.OnAction = "'" & ThisWorkbook.Name & "'!TheMacro"
End With

End Sub

Sub TheMacro()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With

MsgBox "Hello, World"

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"OKLover" wrote in message
...
How to create a toggle button on the menu? like the Bold,
Underline... button.

Chip, you saved my life ~ many thanks :D


"Chip Pearson" ...
Try the following code. It will add an item to the Tools menu.

Sub AddControl()

Dim Ctrl As Office.CommandBarButton
Set Ctrl = Application.CommandBars.FindControl(ID:=30007) _
.Controls.Add(temporary:=True)
With Ctrl
.Caption = "Click Me"
.State = msoButtonUp
.Visible = True
.OnAction = "'" & ThisWorkbook.Name & "'!TheMacro"
End With

End Sub

Sub TheMacro()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With

MsgBox "Hello, World"

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"OKLover" wrote in message
...
How to create a toggle button on the menu? like the Bold, Underline...
button.