Quote:
Originally Posted by MitchR 
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.
-
-
Set xlApp = CreateObject("Excel.Application")
-
Set xlWb = xlApp.Workbooks.Open(XFile)
-
Set xlWs = xlApp.ActiveSheet
-
-
With xlWs
-
.Buttons.Add(.Range("A1").Top, .Range("A1").Left, 35.12, 23.25).Select
-
'where to go from here
-
-
'Excel Macro
-
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
-
-
Sub Button1_Click()
-
TrAX = Worksheets("Waiting on Return").Range("E2")
-
Dim myLink As String
-
myLink = "http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=en_US&tracknum=" & TrAX & ""
-
ShellExecute hWnd, "open", myLink, vbNullString, vbNullString, Empty
-
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.
You can try modifying the
OnAction Property of the Button to point to a specific Macro when the Button is clicked, as in:
-
-
Worksheets(1).Shapes(1).OnAction = "<Macro Name here>"
-
