ExcelBanter - Error in CommandBars

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Error in CommandBars (https://www.excelbanter.com/excel-programming/391979-error-commandbars.html)

Error in CommandBars

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

Error in CommandBars

Error in CommandBars

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
"Trefor" wrote in message
...
|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

Error in CommandBars

Error in CommandBars

Error in CommandBars

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
--
Trefor

"Trefor" wrote:

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?

--
Trefor

"Jim Rech" wrote:

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
"Trefor" wrote in message
...
| 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
| "Trefor" wrote in message
| ...
| |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
|
|
|