By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,285 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

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

100+
P: 126
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!

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro1()
  2. Dim xlApp As Excel.Application
  3. Dim xlWB As Excel.Workbook
  4. Dim xlWS As Excel.Worksheet
  5. Dim myCB As CommandBar
  6. Dim myCBtn1 As CommandBarButton
  7.  
  8. Set xlApp = New Excel.Application
  9. xlApp.Visible = True
  10. xlApp.Interactive = True
  11. Set xlWB = xlApp.Workbooks.Open("C:\ExtractPolicyList1.xls", , False)
  12. Set xlWS = xlWB.Worksheets("SelectPolicies")
  13.  
  14. With xlApp
  15. ' Delete the commandbar if it exists already
  16. On Error Resume Next
  17. .CommandBars("example").Delete
  18. ' Create a new Command Bar
  19. Set myCB = .CommandBars.Add(Name:="example", _
  20.             temporary:=True, Position:=msoBarTop)
  21.  
  22. ' Add button 1 to this bar
  23. Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
  24. With myCBtn1
  25.     .Caption = "Example"
  26.     .Style = msoButtonIconAndCaption  '<- will force the caption text to show on your button
  27.     .FaceId = 1952
  28.     .OnAction = "Example"
  29. End With
  30. ' Show the command bar
  31. myCB.Visible = True
  32. End With
  33. Set xlApp = Nothing
  34. End Sub
Expand|Select|Wrap|Line Numbers
  1. Sub Example()
  2. MsgBox "it works!"
  3. End Sub
Aug 1 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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
Aug 3 '08 #2

100+
P: 126
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 programmatically.
(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.

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro1()
  2. Dim xlApp As Excel.Application
  3. Dim xlWB As Excel.Workbook
  4. Dim xlWS As Excel.Worksheet
  5. Dim myCB As CommandBar
  6. Dim myCBtn1 As CommandBarButton
  7. Dim vbp As Object
  8. Dim newmod As Object
  9. Dim StartLine As Long
  10. Dim cLines As Long
  11.  
  12. Set xlApp = New Excel.Application
  13. xlApp.Visible = True
  14. xlApp.Interactive = True
  15. Set xlWB = xlApp.Workbooks.Open("C:\Book1.xls", , False)
  16. Set xlWS = xlWB.Worksheets("Example")
  17.  
  18. With xlApp
  19. ' Delete the commandbar if it exists already
  20. On Error Resume Next
  21. .CommandBars("example").Delete
  22. ' Create a new Command Bar
  23. Set myCB = .CommandBars.Add(Name:="example", _
  24.             temporary:=True, Position:=msoBarTop)
  25.  
  26. ' Add button 1 to this bar
  27. Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
  28. With myCBtn1
  29.     .Caption = "Example"
  30.     .Style = msoButtonIconAndCaption  '<- will force the caption text to show on your button
  31.     .FaceId = 1952
  32.     .OnAction = "Example"
  33. End With
  34. ' Show the command bar
  35. myCB.Visible = True
  36. End With
  37. Set vbp = xlWB.VBProject
  38. Set newmod = vbp.VBComponents.Add(1)
  39.  
  40. With xlWB.VBProject.VBComponents("Module1").CodeModule
  41.     cLines = .CountOfLines + 1
  42.     .InsertLines cLines, _
  43.         "Sub Example()" & Chr(13) & _
  44.         " Msgbox ""Eureka, it works!""" & Chr(13) & _
  45.         "End Sub"
  46. End With
  47. Set xlApp = Nothing
  48. 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!
Aug 3 '08 #3

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. .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\Project.Xls" would be :
Expand|Select|Wrap|Line Numbers
  1. .OnAction = "'C:\Program Files\Test\Project.Xls'!Module1.Example"
Aug 10 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.