Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 16th, 2010, 11:35 AM
Registered User
 
Join Date: Nov 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Issues trying to add code programatically

Hi, I'm trying to add code to a spreadsheet's worksheets for a Worksheet_Change event and am having problems as the users need to have the "Trust access to Visual Basic Project" turned on in Excels options.

The code I'm using is this

Code:
Sub AddSheetCodeNR(SheetName As String)
Dim nwSheet As Worksheet
Dim UFvbc As VBComponent
Dim name As String
Dim Code As String
Dim rngCell As Range
Dim intLoop As Integer
Dim count As Integer

Set nwSheet = ThisWorkbook.Sheets(SheetName)
    DoEvents
    On Error Resume Next
    
    Set UFvbc = ActiveWorkbook.VBProject.VBComponents(nwSheet.CodeName)
    
    For Each rngCell In Range("rngSheetChangeModule")
        count = UFvbc.CodeModule.CountOfLines + 1
        Code = rngCell.Value
        UFvbc.CodeModule.InsertLines count, Code
    Next rngCell
End Sub
and the code within the named range "rngSheetChangeModule" is

Code:
'Module Created To show when Manual Cells on Manual Sheets were updated last.
'Created By Rowan Ramsay SLF475
'Last Edited on 17/06/08
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strComment As String
    'Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    'Check if its in the Column R if not then Exit Sub
    If Not Intersect(Target, Range("A:Q")) Is Nothing Then Exit Sub
    If Not Intersect(Target, Range("S:IV")) Is Nothing Then Exit Sub
    'Otherwise update the cell in column F of this row
    If Left(Target.Value, 10) = "Updated By" Then Exit Sub
    Target.Offset(0, 1).ClearComments ' Clear any comments
    Target.Offset(0, 1).AddComment "Updated By " & Environ("User") & " : " & Now() ' add new comments with staff ID added from fUserID (Function)
    Target.Offset(0, 1).Value = Now() ' Add the date time stamp now to the cell
    Target.Offset(0, 1).Interior.ColorIndex = 37 ' colour it for VBA
End Sub
Can anyone show me a way to do this withough needing the "Trust access to Visual Basic Project" turned on??

For information I'm using Excel 2003.

Thasnks in advance..... :)

Rowin_Aboat





Similar Threads
Thread Thread Starter Forum Replies Last Post
Issues with Chapter 2 Todo List .add() on key listener Swak BOOK: Professional Android Application Development ISBN: 978-0-470-34471-2 2 January 22nd, 2010 06:43 AM
How to add task in Scheduled Task programatically amitjoshipune C# 2 July 31st, 2008 05:42 AM
How to add a site programatically millerthegorilla SharePoint Development 1 June 17th, 2008 11:43 AM
TextBox issues when created programatically nkommala ASP.NET 1.x and 2.0 Application Design 3 October 9th, 2006 11:59 PM
more book code issues skicrud Javascript How-To 1 June 3rd, 2004 09:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.