470,612 Members | 2,229 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

Access module to insert Excel macro into an excel command button

65 64KB
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
  2. Set xlApp = CreateObject("Excel.Application")
  3. Set xlWb = xlApp.Workbooks.Open(XFile)
  4. Set xlWs = xlApp.ActiveSheet
  6. With xlWs
  7. .Buttons.Add(.Range("A1").Top, .Range("A1").Left, 35.12, 23.25).Select
  8. 'where to go from here
  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
  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
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
1 5919
8,804 Expert 8TB
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
  2. Worksheets(1).Shapes(1).OnAction = "<Macro Name here>"
Jan 29 '09 #2

Post your reply

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

Similar topics

2 posts views Thread by Vinayak Shanbhag via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.