Help with Importing Form Module into external xls. file

[Kevin Daly]

Hello all.
Using XL 2000, I have the following issue:

I created an excel program which hundreds of external offices use and
which includes a user form. I've had to change a couple of lines of
code when a button in the form is pressed. I cannot simply mail them
replacement files, so I need to be able to create a separate program I
can send which will open the file they have and modify the code now in
their system which relates to that userform and button.
Using Chip's website, I have so far what's below, which deletes the
procedure for cmdOK_Click from the frmEntryForm module in the "File1"
workbook:

Sub Delete_OKCommand_Procedure()
Dim VBCodeMod As CodeModule, StartLine As Long, HowManyLines As Long
Set VBCodeMod = Workbooks("File1").VBProject.VBComponents("frmEntryForm").CodeModule
With VBCodeMod
StartLine = .ProcStartLine("cmdOK_Click", vbext_pk_Proc)
HowManyLines = .ProcCountLines("cmdOK_Click",
vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
End Sub

So far so good.

Now I need to be able to replace the code which was just deleted with
the updated code. However, this is over 100 lines, so I can't just
insert these with quotations as Chip's website suggests. I need a way
to store the procedure in the file I'm going to email these guys and
have the program copy it over as as replacement in the file they open.
Essentially, have the procedure I have just deleted REPLACED with new
code sent in a procedure all by itself. This sounds confusing to even
type, but I think it makes sense. Please help. Thanks a bunch! Let me
know if clarification is needed

Kev.

 

[Bob Phillips]

Kevin,

Try deleting the existing module, then re-import a new module

Dim VBComp As Object

Set VBComp = ThisWorkbook.VBProject.VBComponents("Userform1")
ThisWorkbook.VBProject.VBComponents.Remove VBComp

ActiveWorkbook.VBProject.VBComponents.Import _
Filename:="c:\userform1.frm"