Run-Time Error on Macro
 

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").Code Module
.InsertLines .CountOfLines + 1, code
End With
Next k

Thank you for any help with this!!

Les

Run-Time Error on Macro
 

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

By default the VBE is protected from being programmed.

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


--
Jim
"WLMPilot" wrote in message
...
|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").Code Module
| .InsertLines .CountOfLines + 1, code
| End With
| Next k
|
| Thank you for any help with this!!
|
| Les

Run-Time Error on Macro
 

Hi Les, In Excel click ToolsOptionsSecurity, 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.

"WLMPilot" wrote:

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").Code Module
.InsertLines .CountOfLines + 1, code
End With
Next k

Thank you for any help with this!!

Les

Run-Time Error on Macro
 

Thanks,

Les

"Jim Rech" wrote:

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

By default the VBE is protected from being programmed.

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


--
Jim
"WLMPilot" wrote in message
...
|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").Code Module
| .InsertLines .CountOfLines + 1, code
| End With
| Next k
|
| Thank you for any help with this!!
|
| Les

Run-Time Error on Macro
 

Thanks,
Les

"JLGWhiz" wrote:

Hi Les, In Excel click ToolsOptionsSecurity, 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.

"WLMPilot" wrote:

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").Code Module
.InsertLines .CountOfLines + 1, code
End With
Next k

Thank you for any help with this!!

Les