Access VBA to run Macro when an excel toolbar button is clicked

[JFKJr]

Hello everyone, the following is the code which opens an excel file and creates a toolbar button.
And, when I click the toolbar button as shown at line #28, the code should call the "Example" macro. But, it is displaying "The Macro "Example" cannot be found" Message Box.

Can anyone suggest an idea on how to call the macro whenever a user clicks the toolbar button?

Your help will be much appreciated. Thanks!

Sub RunMacro1()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim myCB As CommandBar
Dim myCBtn1 As CommandBarButton
 
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Interactive = True
Set xlWB = xlApp.Workbooks.Open("C:\ExtractPolicyList1.xls", , False)
Set xlWS = xlWB.Worksheets("SelectPolicies")
 
With xlApp
' Delete the commandbar if it exists already
On Error Resume Next
.CommandBars("example").Delete
' Create a new Command Bar
Set myCB = .CommandBars.Add(Name:="example", _
            temporary:=True, Position:=msoBarTop)
 
' Add button 1 to this bar
Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
With myCBtn1
    .Caption = "Example"
    .Style = msoButtonIconAndCaption  '<- will force the caption text to show on your button
    .FaceId = 1952
    .OnAction = "Example"
End With
' Show the command bar
myCB.Visible = True
End With
Set xlApp = Nothing
End Sub

Sub Example()
MsgBox "it works!"
End Sub

[Stewart Ross]

Hi again JFKjr. I have tested your command bar code along with a test message box outputting a "hello world" and it works fine for me.

For sub Example to be called by the command button it must be located in a public code module within the worksheet (in my case in the default Module1) - could you confirm that it is? The Excel worksheet will not be able to access the sub if it is really located in the Access VBA code, or if it is associated with a private code module in the worksheet (e.g. those associated with individual worksheets).

While researching this one I came across a page by the multitalented Dev Ashish (page linked here) which shows how to call an Excel worksheet function from automation code such as code running in Access, using the .Run method of the application object

xlapp.run ("name of sub or function")

I've tried this out on a "Hello World" example message box and it works fine too, but it is not linked to the command button in Excel so is not the solution to what you asked. Anyway, it is interesting so I mention it here for future reference. You may already know of it from your previous marathon thread on Excel/Access interaction.

-Stewart

[JFKJr]

Hi again JFKjr. I have tested your command bar code along with a test message box outputting a "hello world" and it works fine for me.

For sub Example to be called by the command button it must be located in a public code module within the worksheet (in my case in the default Module1) - could you confirm that it is? The Excel worksheet will not be able to access the sub if it is really located in the Access VBA code, or if it is associated with a private code module in the worksheet (e.g. those associated with individual worksheets).

While researching this one I came across a page by the multitalented Dev Ashish (page linked here) which shows how to call an Excel worksheet function from automation code such as code running in Access, using the .Run method of the application object

xlapp.run ("name of sub or function")

I've tried this out on a "Hello World" example message box and it works fine too, but it is not linked to the command button in Excel so is not the solution to what you asked. Anyway, it is interesting so I mention it here for future reference. You may already know of it from your previous marathon thread on Excel/Access interaction.

-Stewart

Hello Stewart, nice to see you again :). Thanks for your reply.

Yes, you are right, I am able to run the "Example" sub above if I place the sub in a worsheet code module which has toolbar button. But I do not want to manually place the proc/sub in the worksheet. I would like to do it programmaticall y.
(FYI: The link from your post shows how to run an excel macro, which is already created in excel, through access VBA)

This is what I am looking for, well I am considering two cases and like to use whichever alternative is possible:

I am able to open an excel file and create a toolbar button through Access VBA, keeping that in mind, I would like know if the following alternatives are possible:

a) call a proc/sub defined in access VBA whenever a user clicks the excel toolbar button.

b) the following code (line #37 to 46) is used to create a sub in worksheet code module through Access VBA and the sub is called whenever a user clicks the excel toolbar button.

But it is only possible if the user checks/selects "Trust access to the VBA project object module" macro security option of the excel file.

Sub RunMacro1()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim myCB As CommandBar
Dim myCBtn1 As CommandBarButton
Dim vbp As Object
Dim newmod As Object
Dim StartLine As Long
Dim cLines As Long
 
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Interactive = True
Set xlWB = xlApp.Workbooks.Open("C:\Book1.xls", , False)
Set xlWS = xlWB.Worksheets("Example")
 
With xlApp
' Delete the commandbar if it exists already
On Error Resume Next
.CommandBars("example").Delete
' Create a new Command Bar
Set myCB = .CommandBars.Add(Name:="example", _
            temporary:=True, Position:=msoBarTop)
 
' Add button 1 to this bar
Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
With myCBtn1
    .Caption = "Example"
    .Style = msoButtonIconAndCaption  '<- will force the caption text to show on your button
    .FaceId = 1952
    .OnAction = "Example"
End With
' Show the command bar
myCB.Visible = True
End With
Set vbp = xlWB.VBProject
Set newmod = vbp.VBComponents.Add(1)
 
 With xlWB.VBProject.VBComponents("Module1").CodeModule
    cLines = .CountOfLines + 1
    .InsertLines cLines, _
        "Sub Example()" & Chr(13) & _
        " Msgbox ""Eureka, it works!""" & Chr(13) & _
        "End Sub"
End With
Set xlApp = Nothing
End Sub

Is it possible to create a sub in worksheet code module without asking the user to check/select "Trust access" macro security option?

Feel free to suggest any other alternative solutions to my problem.

Hope I did not confuse you!

[NeoPa]

If you are setting the .OnAction value to the name of your procedure from a foreign application (Access for instance), I think you may need to specify a module qualifier.
For instance your line #32 (in post #3) should be changed to something like :

.OnAction = "Module1.Example"

If the case is even more extreme then it may need a filename qualifier. The format of that, assuming the spreadsheet is named something like "C:\Program Files\Test\Proj ect.Xls" would be :

.OnAction = "'C:\Program Files\Test\Project.Xls'!Module1.Example"