A macro written by a macro
 

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but still I do
not understand how to write my code (I am not very good at it! :-)

I would like to put this in the worksheet code (thank you Frank!):

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("a1:a100")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo errhandler
Application.EnableEvents = False
With Target
.Offset(0, 1).Value = Application.UserName
.Offset(0, 2).Value = Format(Date, "DD-MMM-YYYY")
End With

errhandler:
Application.EnableEvents = True
End Sub


I've come to this point:

Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

Here I don't know anymore how to express my code!

Could somebody please help me?

Many thanks in advance,
best regards,

--
Valeria

A macro written by a macro
 

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but still I do
not understand how to write my code (I am not very good at it! :-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

A macro written by a macro
 

Hi Frank,
it does not seem to work: I get a syntax error on this line

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"

I actually have the problem with this module writing code that I do not know
where to break the lines and put the ""... I always seem to get these syntax
errors...

Thank you,
Best regards,
Valeria



"Frank Kabel" wrote:

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but still I do
not understand how to write my code (I am not very good at it! :-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

A macro written by a macro
 

Space Underscore to break a line ( _)

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is _
Nothing Then"

Put "" around the exact code you want written to the line.
Mike F
"Valeria" wrote in message
...
Hi Frank,
it does not seem to work: I get a syntax error on this line

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"

I actually have the problem with this module writing code that I do not
know
where to break the lines and put the ""... I always seem to get these
syntax
errors...

Thank you,
Best regards,
Valeria



"Frank Kabel" wrote:

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100"))
Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet
from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but
still I do
not understand how to write my code (I am not very good at it! :-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

A macro written by a macro
 

Hi,
This was unfortunately not the problem - I had already used the underscore
to break the line, it still does not work and gives me the syntax error... do
you know why?
Many thanks in advance,
best regards,
Valeria

"Mike Fogleman" wrote:

Space Underscore to break a line ( _)

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is _
Nothing Then"

Put "" around the exact code you want written to the line.
Mike F
"Valeria" wrote in message
...
Hi Frank,
it does not seem to work: I get a syntax error on this line

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"

I actually have the problem with this module writing code that I do not
know
where to break the lines and put the ""... I always seem to get these
syntax
errors...

Thank you,
Best regards,
Valeria



"Frank Kabel" wrote:

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100"))
Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet
from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but
still I do
not understand how to write my code (I am not very good at it! :-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

A macro written by a macro
 

If you are interested, I found what it was:
Excel does not like the "" alone, for ex. in the range "a1:a100".
If you want Excel to write code containing "" in a module, you have to
double them:

.InsertLines StartLine, "If Intersect(Target, Me.Range(""a1:a100"")) Is
Nothing Then"

It works so!

Best regards,
Valeria


"Valeria" wrote:

Hi,
This was unfortunately not the problem - I had already used the underscore
to break the line, it still does not work and gives me the syntax error... do
you know why?
Many thanks in advance,
best regards,
Valeria

"Mike Fogleman" wrote:

Space Underscore to break a line ( _)

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is _
Nothing Then"

Put "" around the exact code you want written to the line.
Mike F
"Valeria" wrote in message
...
Hi Frank,
it does not seem to work: I get a syntax error on this line

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100")) Is
Nothing Then"

I actually have the problem with this module writing code that I do not
know
where to break the lines and put the ""... I always seem to get these
syntax
errors...

Thank you,
Best regards,
Valeria



"Frank Kabel" wrote:

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100"))
Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a spreadsheet
from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful, but
still I do
not understand how to write my code (I am not very good at it! :-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

A macro written by a macro
 

There were a coupled of problems

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.insertLines StartLine, "If Intersect(Target, Me.Range(""A1:A100"")) Is
Nothing Then"
.insertLines StartLine + 1, "Exit sub"
.insertLines StartLine + 2, "End if"
End With



--

HTH

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


"Valeria" wrote in message
...
If you are interested, I found what it was:
Excel does not like the "" alone, for ex. in the range "a1:a100".
If you want Excel to write code containing "" in a module, you have to
double them:

.InsertLines StartLine, "If Intersect(Target, Me.Range(""a1:a100"")) Is
Nothing Then"

It works so!

Best regards,
Valeria


"Valeria" wrote:

Hi,
This was unfortunately not the problem - I had already used the
underscore
to break the line, it still does not work and gives me the syntax
error... do
you know why?
Many thanks in advance,
best regards,
Valeria

"Mike Fogleman" wrote:

Space Underscore to break a line ( _)

.InsertLines StartLine, "If Intersect(Target, Me.Range("a1:a100"))
Is _
Nothing Then"

Put "" around the exact code you want written to the line.
Mike F
"Valeria" wrote in message
...
Hi Frank,
it does not seem to work: I get a syntax error on this line

.InsertLines StartLine, "If Intersect(Target,
Me.Range("a1:a100")) Is
Nothing Then"

I actually have the problem with this module writing code that I do
not
know
where to break the lines and put the ""... I always seem to get
these
syntax
errors...

Thank you,
Best regards,
Valeria



"Frank Kabel" wrote:

Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "If Intersect(Target,
Me.Range("a1:a100"))
Is
Nothing Then"
.InsertLine Startline+1, "Exit sub"
.insertLine,startline+2, "End if"
'....




"Valeria" wrote:

Dear Experts,
I would like to write a macro in a worksheet code of a
spreadsheet
from
another macro. I have visited Cheap Pearson's page,
http://www.cpearson.com/excel/vbe.htm, which was very useful,
but
still I do
not understand how to write my code (I am not very good at it!
:-)
[...]
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _