Run-Time Error on Macro

[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

 

[Jim Rech]

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

 

[JLGWhiz]

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.

 

[WLMPilot]

Thanks,

Les

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

 

[WLMPilot]

Thanks,
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.