Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how we can run macro after a written text in a column | Excel Discussion (Misc queries) | |||
Create .NET object written in C# in Excel Macro | Excel Discussion (Misc queries) | |||
I have never written a macro | Excel Discussion (Misc queries) | |||
I have never written a macro | Excel Discussion (Misc queries) | |||
How to get Username written in logfile (.txt) through macro | Excel Programming |