Hi All
I have built a custom toolbar (code snippet below), the control button
OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the status,
with a single OK button to cancel the Msgbox. However the user has to press
the OK control twice to cancel the MsgBox. I have established that
subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub

Nigel,

as you may have suspected there's a trick to
passing arguments in OnAction strings :)


.OnAction = "'" & "ExportData ""1"" '"



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel RS wrote in


Hi All
I have built a custom toolbar (code snippet below), the control
button OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the
status, with a single OK button to cancel the Msgbox. However the
user has to press the OK control twice to cancel the MsgBox. I have
established that subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub

PS:

when you create the MenuBar, you should add temporary:=true
similar to what you do when you add the controls


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam




code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()


Set NewMenuBar = CommandBars.Add(MenuBar:=False,temporary:=True)

I wouldn't try to pass a value in that way, I would set the Parameter
property or the Tag proeprty to that value, and test it i the macro

With Application.CommandBars.ActionControl
MsgBox .Tag
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nigel RS" wrote in message
...
Hi All
I have built a custom toolbar (code snippet below), the control button
OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the status,
with a single OK button to cancel the Msgbox. However the user has to
press
the OK control twice to cancel the MsgBox. I have established that
subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub

Hi, keepITcool and Bob
Thanks for the suggestion I will give them a try.

Cheers
Nigel RS



"Bob Phillips" wrote:

I wouldn't try to pass a value in that way, I would set the Parameter
property or the Tag proeprty to that value, and test it i the macro

With Application.CommandBars.ActionControl
MsgBox .Tag
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nigel RS" wrote in message
...
Hi All
I have built a custom toolbar (code snippet below), the control button
OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the status,
with a single OK button to cancel the Msgbox. However the user has to
press
the OK control twice to cancel the MsgBox. I have established that
subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub

Just to add. This is an undocumented feature. As I recall there are at
least some verions of xl - somewhere in the xl2000 service pack n to at least
the first release of xl2002 that do not support this. I don't have
definitive details and I could be wrong, but I recall several post reporting
this feature was failing around the release of those versions.

--
Regards,
Tom Ogilvy


"keepITcool" wrote:

Nigel,

as you may have suspected there's a trick to
passing arguments in OnAction strings :)


.OnAction = "'" & "ExportData ""1"" '"



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel RS wrote in


Hi All
I have built a custom toolbar (code snippet below), the control
button OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the
status, with a single OK button to cancel the Msgbox. However the
user has to press the OK control twice to cancel the MsgBox. I have
established that subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
-------------------------------------------------------------------
Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub

Further to Bob's suggestion:

specify the .Tag or .Parameter for the control
then assign the same OnAction proc to all controls.

Sub MyBarActions()
Select Case CommandBars.ActionControl.Parameter
Case 1
... divers actions for each control
End Select
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel RS wrote in


Hi, keepITcool and Bob
Thanks for the suggestion I will give them a try.

Cheers
Nigel RS



"Bob Phillips" wrote:

I wouldn't try to pass a value in that way, I would set the
Parameter property or the Tag proeprty to that value, and test it i
the macro

With Application.CommandBars.ActionControl
MsgBox .Tag
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nigel RS" wrote in message
...
Hi All
I have built a custom toolbar (code snippet below), the control
button OnAction calls a subroutine with a single parameter.
The subroutine simply displays a MsgBox informing the user of the
status, with a single OK button to cancel the Msgbox. However the
user has to
press
the OK control twice to cancel the MsgBox. I have established
that subroutine runs twice!

Can anyone explain why this happens and how to prevent it?


code snippet follows....
------------------------------------------------------------------
- Sub BuildTB()

Const tbName = "CFAM_TB"
Dim NewMenuBar As CommandBar
Dim NewMenu As CommandBarControl
Dim NewItem As CommandBarControl

Call RemoveMenus(tbName)

Set NewMenuBar = CommandBars.Add(MenuBar:=False)
With NewMenuBar
.Name = tbName
.Visible = True

With .Controls.Add(Type:=msoControlButton, _
temporary:=True)
.FaceId = 2817
.OnAction = "ExportData(1)"
.Caption = "Send All"
.Style = msoButtonIconAndCaption
End With

.Position = msoBarTop
.Protection = msoBarNoChangeVisible + _
msoBarNoResize + _
msoBarNoChangeDock + _
msoBarNoCustomize

End With

End Sub