Run-Time Error on Macro

  • Thread starter Thread starter WLMPilot
  • Start date Start date
W

WLMPilot

I have a macro that works all the way up until it reaches the following code.
Basically, I am trying to create 26 Commandbuttons via a macro (works) and
write the code within the macro for each button to access its respective
sheet.

The error is: Runtime Error 1004: Programmatic access to Visual Basic
Project is not trusted.

The following code is within a For/Next loop (For k = 1 to 26). Code is DIM
as String and I want the code to be for Sheet1. The caption for the button
is the same for its sheet.

code = ""
code = "Private Sub CommandButton" & CStr(k) & "_Click()" & vbCrLf
code = code & "Dim sn as String" & vbCrLf
code = code & "sn = CommandButton" & CStr(k) & ".Caption" & vbCrLf
code = code & "Application.Goto Reference:=Worksheets(" & sn &
").Range('A1')" & vbCrLf
code = code & "End Sub"
'Write code for button
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CountOfLines + 1, code
End With
Next k

Thank you for any help with this!!

Les
 
The error is: Runtime Error 1004: Programmatic access to Visual Basic
By default the VBE is protected from being programmed.

Tools, Macro, Security, Trusted Publishers and check "Trust access to Visual
Basic Project".


--
Jim
|I have a macro that works all the way up until it reaches the following
code.
| Basically, I am trying to create 26 Commandbuttons via a macro (works) and
| write the code within the macro for each button to access its respective
| sheet.
|
| The error is: Runtime Error 1004: Programmatic access to Visual Basic
| Project is not trusted.
|
| The following code is within a For/Next loop (For k = 1 to 26). Code is
DIM
| as String and I want the code to be for Sheet1. The caption for the
button
| is the same for its sheet.
|
| code = ""
| code = "Private Sub CommandButton" & CStr(k) & "_Click()" &
vbCrLf
| code = code & "Dim sn as String" & vbCrLf
| code = code & "sn = CommandButton" & CStr(k) & ".Caption" &
vbCrLf
| code = code & "Application.Goto Reference:=Worksheets(" & sn &
| ").Range('A1')" & vbCrLf
| code = code & "End Sub"
| 'Write code for button
| With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
| .InsertLines .CountOfLines + 1, code
| End With
| Next k
|
| Thank you for any help with this!!
|
| Les
 
Hi Les, In Excel click Tools>Options>Security, then look at the bottom of
the dialog box to see if the checkbox for Allow Access to VBA Projects is
checked. If not, check it.
 
Thanks,

Les

Jim Rech said:
By default the VBE is protected from being programmed.

Tools, Macro, Security, Trusted Publishers and check "Trust access to Visual
Basic Project".


--
Jim
|I have a macro that works all the way up until it reaches the following
code.
| Basically, I am trying to create 26 Commandbuttons via a macro (works) and
| write the code within the macro for each button to access its respective
| sheet.
|
| The error is: Runtime Error 1004: Programmatic access to Visual Basic
| Project is not trusted.
|
| The following code is within a For/Next loop (For k = 1 to 26). Code is
DIM
| as String and I want the code to be for Sheet1. The caption for the
button
| is the same for its sheet.
|
| code = ""
| code = "Private Sub CommandButton" & CStr(k) & "_Click()" &
vbCrLf
| code = code & "Dim sn as String" & vbCrLf
| code = code & "sn = CommandButton" & CStr(k) & ".Caption" &
vbCrLf
| code = code & "Application.Goto Reference:=Worksheets(" & sn &
| ").Range('A1')" & vbCrLf
| code = code & "End Sub"
| 'Write code for button
| With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
| .InsertLines .CountOfLines + 1, code
| End With
| Next k
|
| Thank you for any help with this!!
|
| Les
Click to expand...
 
Thanks,
Les

JLGWhiz said:
Hi Les, In Excel click Tools>Options>Security, then look at the bottom of
the dialog box to see if the checkbox for Allow Access to VBA Projects is
checked. If not, check it.
Click to expand...
 
Back
Top