Results 1 to 2 of 2

Excel closes when using VBA to write VBA code

Threaded View

  1. #1
    Registered User
    Join Date
    06-04-2007
    Posts
    1

    Excel closes when using VBA to write VBA code

    I'm trying to write a macro that will update the class module for a select number of sheets in 131 other workbooks. The variable being passed in is the sheets codename. It seems that the module is updated but a fatal error happens when the end with line is run while stepping through the code. Any ideas?

    Private Sub code_sheet(sheetname) 
    
    code = "Private Sub Worksheet_Activate()" & vbCrLf 
    code = code & "filename = ""'"" + ActiveWorkbook.Name + ""'!findid""" & vbCrLf 
    code = code & "Application.Run filename" & vbCrLf 
    code = code & "End Sub" & vbCrLf & vbCrLf 
    code = code & "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf 
    code = code & "filename = ""'"" + ActiveWorkbook.Name + ""'!findid""" & vbCrLf 
    code = code & "Application.Run filename" & vbCrLf 
    code = code & "End Sub" & vbCrLf & vbCrLf 
    code = code & "Private Sub Worksheet_Deactivate()" & vbCrLf 
    code = code & "Application.StatusBar = False" & vbCrLf 
    code = code & "End Sub" 
    With ActiveWorkbook.VBProject.VBComponents(sheetname).CodeModule 
         nextline = .CountOfLines + 1 
         .InsertLines nextline, code 
    End With 
    End Sub
    Last edited by VBA Noob; 06-04-2007 at 01:19 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1