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

Access module to insert Excel macro into an excel command button

MitchR
P: 63
Good Morning Folks;

I have a question that is pretty far fetched but here goes nothing... I am looking to find a way to insert a macro into an Excel command button located in an Access VBA generated Excel spreadsheet from an Access Module. I can create the spreadsheet, button, and I have the macro to insert into command button. But I am not sure how to assign the macro to the button in the access module.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Set xlApp = CreateObject("Excel.Application")
  3. Set xlWb = xlApp.Workbooks.Open(XFile)
  4. Set xlWs = xlApp.ActiveSheet
  5.  
  6. With xlWs
  7. .Buttons.Add(.Range("A1").Top, .Range("A1").Left, 35.12, 23.25).Select
  8. 'where to go from here
  9.  
  10. 'Excel Macro
  11. Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
  12.  
  13. Sub Button1_Click()
  14. TrAX = Worksheets("Waiting on Return").Range("E2")
  15. Dim myLink As String
  16. myLink = "http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=en_US&tracknum=" & TrAX & ""
  17. ShellExecute hWnd, "open", myLink, vbNullString, vbNullString, Empty
  18. End Sub
  19.  
As always Thank you very much for taking a look.I know it is far fetched. I just wanted to see how far I can push the envelope.
Jan 28 '09 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
@MitchR
You can try modifying the OnAction Property of the Button to point to a specific Macro when the Button is clicked, as in:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Worksheets(1).Shapes(1).OnAction = "<Macro Name here>"
  3.  
Jan 29 '09 #2

Post your reply

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