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.
- Sub RunMacro1()
-
Dim xlApp As Excel.Application
-
Dim xlWB As Excel.Workbook
-
Dim xlWS As Excel.Worksheet
-
Dim myCB As CommandBar
-
Dim myCBtn1 As CommandBarButton
-
Dim vbp As Object
-
Dim newmod As Object
-
Dim StartLine As Long
-
Dim cLines As Long
-
-
Set xlApp = New Excel.Application
-
xlApp.Visible = True
-
xlApp.Interactive = True
-
Set xlWB = xlApp.Workbooks.Open("C:\Book1.xls", , False)
-
Set xlWS = xlWB.Worksheets("Example")
-
-
With xlApp
-
' Delete the commandbar if it exists already
-
On Error Resume Next
-
.CommandBars("example").Delete
-
' Create a new Command Bar
-
Set myCB = .CommandBars.Add(Name:="example", _
-
temporary:=True, Position:=msoBarTop)
-
-
' Add button 1 to this bar
-
Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
-
With myCBtn1
-
.Caption = "Example"
-
.Style = msoButtonIconAndCaption '<- will force the caption text to show on your button
-
.FaceId = 1952
-
.OnAction = "Example"
-
End With
-
' Show the command bar
-
myCB.Visible = True
-
End With
-
Set vbp = xlWB.VBProject
-
Set newmod = vbp.VBComponents.Add(1)
-
- With xlWB.VBProject.VBComponents("Module1").CodeModule
-
cLines = .CountOfLines + 1
-
.InsertLines cLines, _
-
"Sub Example()" & Chr(13) & _
-
" Msgbox ""Eureka, it works!""" & Chr(13) & _
-
"End Sub"
-
End With
-
Set xlApp = Nothing
-
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!