A macro written by a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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").CodeModule
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,
 
Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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"
'....
 
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 said:
Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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 said:
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").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Click to expand...
Click to expand...
 
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 said:
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 said:
Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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 said:
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").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Hi Valeria
(not tested) but try:
Sub test()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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"
'....




:

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").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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



:

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

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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"
'....




:

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").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Click to expand...
Click to expand...
Click to expand...
 
There were a coupled of problems

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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 said:
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 said:
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 said:
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
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



:

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

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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"
'....




:

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").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

code to delete vba code 1
Subscript out of range 7
VB editor opens by magic 4
Xl 2007 v 2003 Re Populate ThisWorkbook via VBA 2
Creating An Event Procedure 4
Worksheet in VB COMPONENT 3
Excel crashes when programming to the VBA Editor 2
Creating ThisWorkbook Event Procedures 2

Back
Top