Error in CommandBars

G

Guest

I am getting an error:

Run-time error '-2147467259 (80004005)'
Method 'OnAction' of object '_CommandBarButton' failed


The error occurs in the following code:

Sub Create_DCA_Menu()
' Build Opening Menu Bar
Delete_DCA_Menu
Dim mynewbar, button1, button2, button3, button4, button5, mysubmenu
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
.Caption = "DCA Menu"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Open Menu"
.OnAction = ThisWorkbook.Name & "!Open_Menu" <--- The occurs here.
End With

Set button2 = mynewbar.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Setup Menu"
.OnAction = ThisWorkbook.Name & "!Setup_Menu"
End With

Set button3 = mynewbar.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Connection Record Validation"
.OnAction = "DisplayConnectionForm"
End With


Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Help"
End With

Set button4 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button4
.Caption = "Send Help Email"
.OnAction = ThisWorkbook.Name & "!Prepare_Help_Email"
End With

Set button5 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button5
.Caption = "View Manual"
.OnAction = ThisWorkbook.Name & "!ViewHelpFile"
End With

End Sub

This code is in an Add-in and what makes this confusing is this sub used to
work fine and in fact if I run this in edit mode from the source .xls to the
Add-In, it works fine.

Any help would be very much appreciated.
 
J

Jim Rech

.OnAction = ThisWorkbook.Name & "!Setup_Menu"

I would think you could get by with this as the add-in knows to look in
itself for the code:

.OnAction = "Setup_Menu"

As to the error, I'd guess you saved the workbook/addin with a name
containing spaces so it (ThisWorkbook.Name) would have to be enclosed in
quotes.

--
Jim
|I am getting an error:
|
| Run-time error '-2147467259 (80004005)'
| Method 'OnAction' of object '_CommandBarButton' failed
|
|
| The error occurs in the following code:
|
| Sub Create_DCA_Menu()
| ' Build Opening Menu Bar
| Delete_DCA_Menu
| Dim mynewbar, button1, button2, button3, button4, button5, mysubmenu
| Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
| Temporary:=True)
| With mynewbar
| .Caption = "DCA Menu"
| End With
|
| Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
| With button1
| .Caption = "Open Menu"
| .OnAction = ThisWorkbook.Name & "!Open_Menu" <--- The occurs here.
| End With
|
| Set button2 = mynewbar.Controls.Add(Type:=msoControlButton)
| With button2
| .Caption = "Setup Menu"
| .OnAction = ThisWorkbook.Name & "!Setup_Menu"
| End With
|
| Set button3 = mynewbar.Controls.Add(Type:=msoControlButton)
| With button3
| .Caption = "Connection Record Validation"
| .OnAction = "DisplayConnectionForm"
| End With
|
|
| Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
| With mysubmenu
| .Caption = "Help"
| End With
|
| Set button4 = mysubmenu.Controls.Add(Type:=msoControlButton)
| With button4
| .Caption = "Send Help Email"
| .OnAction = ThisWorkbook.Name & "!Prepare_Help_Email"
| End With
|
| Set button5 = mysubmenu.Controls.Add(Type:=msoControlButton)
| With button5
| .Caption = "View Manual"
| .OnAction = ThisWorkbook.Name & "!ViewHelpFile"
| End With
|
| End Sub
|
| This code is in an Add-in and what makes this confusing is this sub used
to
| work fine and in fact if I run this in edit mode from the source .xls to
the
| Add-In, it works fine.
|
| Any help would be very much appreciated.
|
| --
| Trefor
 
G

Guest

Jim,

Thankyou for your reply.

I replaced the failing line with your exact text and it still fails. If as
you say this was the problem, why would this have worked in the past and why
would it work from the .xls containing exactly the same code?
 
J

Jim Rech

I can't say why it's not working as it should. "It" meaning the assignment
of a string to the OnAction property of a control.

You should do yourself a big favor and dim objects as a specific type like
CommandbarControl. Also use Option Explicit if you're not.

If you have a simplified example in a workbook that's failing I will look at
it if you care to email it.

--
Jim
| Jim,
|
| Thankyou for your reply.
|
| I replaced the failing line with your exact text and it still fails. If as
| you say this was the problem, why would this have worked in the past and
why
| would it work from the .xls containing exactly the same code?
|
| --
| Trefor
|
|
| "Jim Rech" wrote:
|
| > >> .OnAction = ThisWorkbook.Name & "!Setup_Menu"
| >
| > I would think you could get by with this as the add-in knows to look in
| > itself for the code:
| >
| > .OnAction = "Setup_Menu"
| >
| > As to the error, I'd guess you saved the workbook/addin with a name
| > containing spaces so it (ThisWorkbook.Name) would have to be enclosed in
| > quotes.
| >
| > --
| > Jim
| > | > |I am getting an error:
| > |
| > | Run-time error '-2147467259 (80004005)'
| > | Method 'OnAction' of object '_CommandBarButton' failed
| > |
| > |
| > | The error occurs in the following code:
| > |
| > | Sub Create_DCA_Menu()
| > | ' Build Opening Menu Bar
| > | Delete_DCA_Menu
| > | Dim mynewbar, button1, button2, button3, button4, button5,
mysubmenu
| > | Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
| > | Temporary:=True)
| > | With mynewbar
| > | .Caption = "DCA Menu"
| > | End With
| > |
| > | Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
| > | With button1
| > | .Caption = "Open Menu"
| > | .OnAction = ThisWorkbook.Name & "!Open_Menu" <--- The occurs
here.
| > | End With
| > |
| > | Set button2 = mynewbar.Controls.Add(Type:=msoControlButton)
| > | With button2
| > | .Caption = "Setup Menu"
| > | .OnAction = ThisWorkbook.Name & "!Setup_Menu"
| > | End With
| > |
| > | Set button3 = mynewbar.Controls.Add(Type:=msoControlButton)
| > | With button3
| > | .Caption = "Connection Record Validation"
| > | .OnAction = "DisplayConnectionForm"
| > | End With
| > |
| > |
| > | Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
| > | With mysubmenu
| > | .Caption = "Help"
| > | End With
| > |
| > | Set button4 = mysubmenu.Controls.Add(Type:=msoControlButton)
| > | With button4
| > | .Caption = "Send Help Email"
| > | .OnAction = ThisWorkbook.Name & "!Prepare_Help_Email"
| > | End With
| > |
| > | Set button5 = mysubmenu.Controls.Add(Type:=msoControlButton)
| > | With button5
| > | .Caption = "View Manual"
| > | .OnAction = ThisWorkbook.Name & "!ViewHelpFile"
| > | End With
| > |
| > | End Sub
| > |
| > | This code is in an Add-in and what makes this confusing is this sub
used
| > to
| > | work fine and in fact if I run this in edit mode from the source .xls
to
| > the
| > | Add-In, it works fine.
| > |
| > | Any help would be very much appreciated.
| > |
| > | --
| > | Trefor
| >
| >
| >
 
G

Guest

Jim,

Many thanks for your reply. I ran the spreadsheet on another machine and it
works fine. I deleted everything I could think of on the first machine copied
the code from the other machine and it still fails!!!

So what on earth would cause this error that is machine specific?
 
G

Guest

Jim,

I forgot to mention I added all of your suggestions (I hope). The entire
module is listed below.

Currently this is the status:

1. This module copied into a new workbook works fine
2. This module saved as an Add-in works fine
3. This module in the original workbook on my laptop works fine
4. This module in the original workbook on my desktop fails
5. I deleted every other module and form in this Add-in and it still fails.

<start of module>
Option Explicit
Option Private Module

Sub Create_DCA_Menu()
' Build Opening Menu Bar
Delete_DCA_Menu
Dim mynewbar As CommandBarControl, button1 As CommandBarButton, button2
As CommandBarButton, button3 As CommandBarButton, button4 As
CommandBarButton, button5 As CommandBarButton, mysubmenu As CommandBarControl
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
.Caption = "DCA Menu"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Open Menu"
.OnAction = ThisWorkbook.Name & "!Open_Menu"
End With

Set button2 = mynewbar.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Setup Menu"
.OnAction = ThisWorkbook.Name & "!Setup_Menu"
End With

Set button3 = mynewbar.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Connection Record Validation"
.OnAction = "DisplayConnectionForm"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Help"
End With

Set button4 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button4
.Caption = "Send Help Email"
.OnAction = ThisWorkbook.Name & "!Prepare_Help_Email"
End With

Set button5 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button5
.Caption = "View Manual"
.OnAction = ThisWorkbook.Name & "!ViewHelpFile"
End With

End Sub

Sub DisplayConnectionForm()
Menu.InstallValidateConnectionRecord_Click
End Sub

Sub Delete_DCA_Menu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("DCA Menu").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Delete_DCA_Menu
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Command Bar Creation 2
Excel 2013 menubar incompatibility? 1
Excel 2003 Commandbars 7
Using Excel 2007 with Excel 2003 template 1
Can anyone help toolbar struggle 5
Nested Sub Menus 3
CommandBar and Controls (how to create separate "Sets" of Controls) 4
Subscript out of range error? 3

Top