Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Access to VB Project not trusted

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access to VB Project not trusted

    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

  2. #2
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Access to VB Project not trusted

    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
    Vladimir Zakharov
    Microsoft MVP Excel

  3. #3
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    721
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access to VB Project not trusted

    Quote Originally Posted by shwalk View Post
    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.
    ----------------------------------------------------
    Theres so much more to discover and so much more to learn.
    -----------------------------------------------------
    Instead of posting your whole problem on the forum, try writing or recording the code yourself and ask only the part that u don't understand or have errors. Try learning VBA. Its Awesome!!

  4. #4
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access to VB Project not trusted

    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.

  5. #5
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,356
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Access to VB Project not trusted

    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 by Kyle123; Oct 11th, 2012 at 03:51 AM.

  6. #6
    Board Regular
    Join Date
    Nov 2009
    Posts
    567
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access to VB Project not trusted

    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 TestButton_Click()~Tester~End Sub", "~", vbCr)
      End With
    End Sub
    Last edited by snb_; Oct 11th, 2012 at 04:10 AM.

  7. #7
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,356
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Access to VB Project not trusted

    @snb I'd prefer not writing code with code, without good reason, it's ugly and hacky

  8. #8
    Board Regular
    Join Date
    Nov 2009
    Posts
    567
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access to VB Project not trusted

    @kyle

    I agree , but if you can't resist the temptation I'd prefer simple code. (e.g. 'addfromstring' instead of 'insertlines')

  9. #9
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access to VB Project not trusted

    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com