| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| Run time error Macro | Excel Programming | |||
| Macro Run-time error | Excel Discussion (Misc queries) | |||
| Macro time out error | Excel Worksheet Functions | |||
Linear Mode
