Importing a CLS file into a macro.

Posted by Rich Finn on May 12, 2000 12:09 PM

Does anyone know a way to import code for an event into a project?

I have a file that contains a Workbook_SheetChange procedure that I need to automatically import into a work book.

I've tried using the following command:

ActiveWorkbook.VBProject.VBComponents.Import ("C:\user\excel\ThisWorkbook.cls")

But this doesn't import the code into the right place. Any ideas?

Thanks in advance.
Rich.

Posted by Ivan Moala on May 13, 2000 1:32 AM

Hi Rich
Have a look @ the following Macro which I adapted
from one of Chip pearsons routines.
For more help have a look @ his excellent site.
http://www.cpearson.com/excel/

Sub Modify_Modules()

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim C As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

C = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your ChangeEvent Procedure OR SubRoutine
SubName = SubName & "Private Sub Workbook_SheetChange(ByVal Sh As Object," & _
"ByVal Target As Excel.Range)" & LF

'Your Procedure
Proc = "If Target.Row = 1 Then" & LF
Proc = Proc & Tabs & "MsgBox " & C & "Testing row number =" & C & " & Target.Address" & LF
Proc = Proc & "End If" & LF

'Use activeWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub

Ivan