Access to VB Project not trusted

shwalk

Board Regular
Joined
Mar 8, 2011
Messages
61
When I try to run the following code I get "Run-time error 1004 Programmatic access to Visual Basic Project is not trusted". Does anyone know the setting to change or way around that error?
Sub CreateButton()
Dim Obj As Object
Dim Code As String
Sheets("Sheet1").Select
'create button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)
Obj.Name = "TestButton"
'buttonn text
ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"
'macro text
Code = "Sub ButtonTest_Click()" & vbCrLf
Code = Code & "Call Tester" & vbCrLf
Code = Code & "End Sub"
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
.insertlines .CountOfLines + 1, Code
End With
End Sub
Sub Tester()
MsgBox "You have click on the test button"
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Excel 2003:
1. Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

Excel 2007
1. Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click Application Options.
2. Click the Trust Center tab, and then click Trust Center Settings.
3. Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.
4. Click OK
 
Upvote 0
When I try to run the following code I get "Run-time error 1004 Programmatic access to Visual Basic Project is not trusted".

Try this
(Excel 2007)

Gotot Excel options --> Trust Center --> Trust Center Settings --> Macro Settings.
Check Trust Access to the VBA project object model.

Basically u r writing a code using code. for which u need to have permission.
 
Upvote 0
Thankyou!! That worked. Apologies for the bad code ... this line Code = "Sub ButtonTest_Click()" & vbCrLf should be Code = "Sub TestButton_Click()" & vbCrLf ... plus more code needs writing to prevent ambiguity if the macro is run multiple times.
 
Upvote 0
Why don't you just hardcode the sub ButtonTest_click() seems like a lot of work doing it how you are. It's only the writing the sub that needs programming access, adding the button doesn't
 
Last edited:
Upvote 0
I'd prefer:

Code:
Sub CreateButton()
  With Sheets("Sheet1") 
    With .OLEObjects.Add("Forms.CommandButton.1", , , , , , , 200, 100, 100, 35)
      .Name = "TestButton"
      .Object.Caption = "Test Button"
    End With
    .Parent.VBProject.VBComponents("sheet1").CodeModule.AddFromString Replace("Sub [COLOR=#ff0000]TestButton[/COLOR]_Click()~Tester~End Sub", "~", vbCr)
  End With
End Sub
 
Last edited:
Upvote 0
@snb I'd prefer not writing code with code, without good reason, it's ugly and hacky ;)
 
Upvote 0
@kyle

I agree :) , but if you can't resist the temptation I'd prefer simple code. (e.g. 'addfromstring' instead of 'insertlines')
 
Upvote 0
I was having trouble with autoshapes in Excel 2007 and thought I might need to use ActiveX controls, but didn't know how to automate adding the code behind them. However I've since found another solution for autoshapes with assigned macros and multiple parameters to pass.

As for automating code write, think I'll stick with the copy sheet method which I use to control Application.TransitionNavigKeys for a worksheet I recreate that has hyperlinks with a destination I want in the top-left of screen (can't get scroll to work the way I want). But because Application.TransitionNavigKeys sets keyboard commands back to Lotus 1-2-3 it's a pain, so I only use it for one worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top