Creating xlsm file with macro code from Excel

[PaulZak]

I have an Excel file (our front-end) that we'd like to create another file with a sheet of data that has some Option Buttons. The option buttons need to be assigned a macro to perform actions. My system typically generates .xlsx files just fine and now I must create an .xlsm and add macro code to it...but a user is getting an error. (We're both running Excel 2007)

I've written the following simple code that runs on my box but not for the first test user. (I'm off-site and the users have far more security lockdowns on their systems.) The code that inserts the macros uses a reference and the user has confirmed it appears to be present and used in the file on their side, too. But when following proc. runs, an error appears. The option buttons are there but no macros exist in file to be assigned to them.

Originally, I was creating the file in a subfolder that may not be trusted so I changed the code to create the file in the same folder as project file, which I'm sure is trusted. The error still appears.

Users can manually create & save xlsm files. Seems I just can't get this to work using VBA.

*******************
Error: 1004
Description: Programmatic Access to VBA project not trusted
*******************

Before this file runs, the new file is created and the Option buttons added. Then for each Option button, I call this procedure to insert a macro, assigned to the Option button. What, more specifically, is this error saying and why would it run fine for me but NOT another user?

Anyone have any ideas???

===================================================
Public Sub Add_VBAmacroToAnotherFile(strFileName As String, _
strMacroText As String)

'IMPORANT: This code requires loading Reference Library:
' "Microsoft Visual Basics for Applications Extensibility x"

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent
'____________________________________________________________

On Error GoTo Err_ERROR

Workbooks(strFileName).Activate

Set VBP = Workbooks(strFileName).VBProject

Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule

VBModule.AddFromString (strMacroText)

End Sub

 

[Warship]

Have the box with the error check:
Excel Options/Trust Center/Trust Center settings/Macro Settings
Check the box: Trust Access to VBA...

 

[PaulZak]

Thanks for your post & good point.

That occurred to me, too, soon after posting and I've asked the user to check. Waiting for a reply. I seem to recall that it is unchecked and cannot be checked on-site due settings constraints by the IT Dept.

If that is the case, I guess I'm sunk and can't ever run such code there...unless someone out there has another idea for all together different code that can generate an xlsm file and insert macros into it using VBA.

 

[Warship]

Would it be possible to set up a Workbook Template with OptionButtons and VBA already in place? Then open the template, import data and save as needed?

 

[PaulZak]

That's a good idea. Thanks!

I'll give that some thought. My first hesitation is that I'd like to keep the app's file set as small as possible. The template would be one more file the user would need to make sure they have in order to run the app. And it would be something I'd need to ensure they don't mess with (edit, rename, delete, etc.) or code issues could arrise. This type of approach seems to me to work best if I'm designing the app for my own, solitary use.