I am getting a error with the following line (full sub is below):
Set cb = c.Controls.Add(msoControlButton, 5)

setting this (msoControlButton, 5) to (msoControlButton, 1) does not result
in an error message, but the button does not seem to execute the on action
event

Any help is much appreciated. Running Excel 2007




Sub Auto_Open()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Refresh Data"
cb.FaceId = 159
cb.OnAction = "InitializeDataInput2"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Generate Reports"
cb.FaceId = 433
cb.OnAction = "ProcessReportSet01"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Print Reports"
cb.FaceId = 4
cb.OnAction = "PrintSet01"

Set cb = c.Controls.Add(msoControlButton, 5)
cb.Style = msoButtonIconAndCaption
cb.Caption = "eMail Reports"
cb.FaceId = 258
cb.OnAction = "CreateAndEmailReports"

End Sub

The second argument applies a built-in control identified by its ID number.
The following exist
2 &Spelling...
3 &Save
4 &Print...

but there's no id:=5

Unless you want a particular built-in control, dispense with that argument
altogether or simply use 1

Regards,
Peter T


"Tom Joseph" wrote in message
...
I am getting a error with the following line (full sub is below):
Set cb = c.Controls.Add(msoControlButton, 5)

setting this (msoControlButton, 5) to (msoControlButton, 1) does not
result
in an error message, but the button does not seem to execute the on action
event

Any help is much appreciated. Running Excel 2007




Sub Auto_Open()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Refresh Data"
cb.FaceId = 159
cb.OnAction = "InitializeDataInput2"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Generate Reports"
cb.FaceId = 433
cb.OnAction = "ProcessReportSet01"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Print Reports"
cb.FaceId = 4
cb.OnAction = "PrintSet01"

Set cb = c.Controls.Add(msoControlButton, 5)
cb.Style = msoButtonIconAndCaption
cb.Caption = "eMail Reports"
cb.FaceId = 258
cb.OnAction = "CreateAndEmailReports"

End Sub

Thanks, Peter. I appreciate it.

"Peter T" wrote:

The second argument applies a built-in control identified by its ID number.
The following exist
2 &Spelling...
3 &Save
4 &Print...

but there's no id:=5

Unless you want a particular built-in control, dispense with that argument
altogether or simply use 1

Regards,
Peter T


"Tom Joseph" wrote in message
...
I am getting a error with the following line (full sub is below):
Set cb = c.Controls.Add(msoControlButton, 5)

setting this (msoControlButton, 5) to (msoControlButton, 1) does not
result
in an error message, but the button does not seem to execute the on action
event

Any help is much appreciated. Running Excel 2007




Sub Auto_Open()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Refresh Data"
cb.FaceId = 159
cb.OnAction = "InitializeDataInput2"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Generate Reports"
cb.FaceId = 433
cb.OnAction = "ProcessReportSet01"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Print Reports"
cb.FaceId = 4
cb.OnAction = "PrintSet01"

Set cb = c.Controls.Add(msoControlButton, 5)
cb.Style = msoButtonIconAndCaption
cb.Caption = "eMail Reports"
cb.FaceId = 258
cb.OnAction = "CreateAndEmailReports"

End Sub