473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

126 100+
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
3 7631
Stewart Ross
2,545 Expert Mod 2GB
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
JFKJr
126 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Rich Anton | last post by:
I am a newbie to Access. I have used Lotus Approach much over the years, but not Access. What I am trying to do is to create an opening screen with a bar menu that will basically allow me to...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
1
by: Bon | last post by:
Hello all I create a form with three buttons in MS Access 2000. They are Open Excel Template, Save Draft and Save Final. When I click the Open Excel Template button, the Excel template will be...
3
by: Deb | last post by:
I am doing something wrong, but I don't know what! I converted some of my macros to modules -- but they won't run. I debugged/compiled but the run command still is greyed out. What do I need...
37
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why...
1
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access...
2
by: bandkindy | last post by:
To preface, I am pretty green on the whole sql thing, but am learning quickly so please bear with me. I have an excel file that is copied to 5 users directories so they have their own working...
1
by: bandkindy | last post by:
To preface, I am pretty green on the whole sql thing, but am learning quickly so please bear with me. I have an excel file that is copied to 5 users directories so they have their own working...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.