Hi, all!
An macro from Personal.xls create a new sheet, now i need another macro
available in that new sheet. How do I do it, that macro create another
macro?
Reg, VK

If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" wrote in message
...
Hi, all!
An macro from Personal.xls create a new sheet, now i need another macro
available in that new sheet. How do I do it, that macro create another
macro?
Reg, VK

If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

"VK" wrote in message
...
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

Would like todo same with about 5 multi-page routines, so wonder if instead
of embedding the new code in the copying macro (per above), if its possible
to copy an existing macro between files, say from personal.xls to book1.xls
??

ie:

Sub test()
Workbooks("Book1").VBProject.VBComponents.Import _
(Workbooks("Personal").VBProject.vbcompoents("agin g_report").codemodule)
End Sub

seems logical, but returns "runtime error 438: object does not support this
property or method"

You cannot import a module from another workbook, you must import from a
file. So you export from Personal.xls to a file, and then import that file
into another book.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jef Gorbach" wrote in message
...

"VK" wrote in message
...
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


Thanks! I'll try it out
Reg. VK

Would like todo same with about 5 multi-page routines, so wonder if
instead
of embedding the new code in the copying macro (per above), if its
possible
to copy an existing macro between files, say from personal.xls to
book1.xls
??

ie:

Sub test()
Workbooks("Book1").VBProject.VBComponents.Import _

(Workbooks("Personal").VBProject.vbcompoents("agin g_report").codemodule)
End Sub

seems logical, but returns "runtime error 438: object does not support
this
property or method"

Bob Phillips wrote:
If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


It works with simple macros. I'm using on another mashine some add-on
for Excel with special code to interact with another application. When
"parent" macro try to append another macro to VB Project, I'm getting
errors. It cannot compile unknown commands from add-on. Is it possible
to disable compilation for this part of code?

Thanks anyway!

Reg, VK

Why not load add-in?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" wrote in message
...
Bob Phillips wrote:
If it is an event macro, use something like

Dim iStartAs Long

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With


It works with simple macros. I'm using on another mashine some add-on
for Excel with special code to interact with another application. When
"parent" macro try to append another macro to VB Project, I'm getting
errors. It cannot compile unknown commands from add-on. Is it possible
to disable compilation for this part of code?

Thanks anyway!

Reg, VK

Because it is on another machine

This is a page on Chip Pearson's website which discusses copying macro
between workbooks which you both may find helpful

http://www.cpearson.com/excel/vbe.htm

Hope this helps

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=37436

I just tried to reproduce what you get but I couldn't.

Can you post the code that failed so that I can try it? I have a couple of
ideas.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Onfrek" wrote in message
oups.com...
Because it is on another machine