VBE Help
 

Hi Tom, i have had a look at Chip Pearsons site and have tried to make
my own code but with no luck. Do you think you could give me some
pointers and help ?


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

If you want to add an event to an existing workbook using code then Chip's
site gives a specific example. Beyond that, what type of help are you
looking for? I also suggested you just keep a record of the before and
then compare it to the after to highlight the changes. This seems simple
and something you should be capable of doing.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
Hi Tom, i have had a look at Chip Pearsons site and have tried to make
my own code but with no luck. Do you think you could give me some
pointers and help ?


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

The code that i have tried is as below from his web page, it works great
but i need to add my code to the ActiveWorksheet of the activeWorkbook
and i am not sure how to do this.

Sub InsertProc()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub


Thanks Tom, i know this probably simple programming but it boggles my
mind !!

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

This code is what i need to put into the ActiveSheet

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = ActiveSheet.Columns("H:H")
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub
-------------------------------------------------------------
I tried the code below and get an error at THE StartLine "EVENT HANDLER
IS INVALID"

Sub InsertProc1()

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


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Sub InsertProc()

Dim StartLine As Long
sName = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub

Makes it the Change event of the the ActiveWorksheet.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
The code that i have tried is as below from his web page, it works great
but i need to add my code to the ActiveWorksheet of the activeWorkbook
and i am not sure how to do this.

Sub InsertProc()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub


Thanks Tom, i know this probably simple programming but it boggles my
mind !!

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Thanks Tom, really appreciate your help.

regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi Tom, sorry i am back, i changed the code to the following and then
excel closes due to an encounted error ?!! It puts in the the following
with no errors.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


But then bombs out !!

My code is as follows.

Sub InsertProc()

Dim StartLine As Long
sname = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sname).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet")
.InsertLines 1, "Dim VRange As Range"
.InsertLines 2, "VRange = ActiveSheet.Columns(""H:H"")"
.InsertLines 3, "Target.Interior.ColorIndex = 37"
.InsertLines 4, "Target.Interior.Pattern = xlSolid"
End With
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Sub InsertProc()

Dim StartLine As Long
sname = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sname).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, "Dim VRange As Range"
.InsertLines StartLine + 1, "Set VRange = ActiveSheet.Columns(""H:H"")"
.InsertLines StartLine + 2, "Target.Interior.ColorIndex = 37"
.InsertLines StartLine + 3, "Target.Interior.Pattern = xlSolid"
End With
End Sub

--
Regards,
Tom Ogilvy

"Les Stout" wrote in message
...
Hi Tom, sorry i am back, i changed the code to the following and then
excel closes due to an encounted error ?!! It puts in the the following
with no errors.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


But then bombs out !!

My code is as follows.

Sub InsertProc()

Dim StartLine As Long
sname = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sname).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet")
.InsertLines 1, "Dim VRange As Range"
.InsertLines 2, "VRange = ActiveSheet.Columns(""H:H"")"
.InsertLines 3, "Target.Interior.ColorIndex = 37"
.InsertLines 4, "Target.Interior.Pattern = xlSolid"
End With
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi Tom, I tried your new code and still get the result below, i just
cant understand it ?

"Microsoft Excel has encountered a problem and needs to close. We are
sorry for the inconvenience."

Do i perhaps have to declare references or something ?

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

the code worked fine for me. I created no references. the event created
worked fine after I executed the code.

I am testing in xl2003. I have had problems with CreateEventProc in the
past in some earlier versions. Try it on a new workbook.

--
Regards,
Tom Ogilvy

"Les Stout" wrote in message
...
Hi Tom, I tried your new code and still get the result below, i just
cant understand it ?

"Microsoft Excel has encountered a problem and needs to close. We are
sorry for the inconvenience."

Do i perhaps have to declare references or something ?

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi Tom, i tried it with stepping into it with F8 and then F5 and it runs
perfectly, it is when i try and step through the proceedure with F8 ?
If i would like to change the font color instead of the background,
would i then change it in the following way

Target.Interior.ColorIndex = 37

to

Target.Font.ColorIndex = 37


Thanks a million again for your patience and assistance.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Just for the record i am using xl2002.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi Les,
I don't really know how Tom figured out what you wanted to start out
with since you did not reference a specific page on Chip Pearson's site
or really explain what you wanted to do. It seemed to me that question
was a continuation of another question but I didn't see your name in a
recent question in the newsgroup either. Yes i see the words
"make my own code" (chip's vbe.htm, I presume) but that is not much
for the rest of us go pick up on.

Tom's code worked for me, I had to define a
Dim sname As String
because I have Option Explicit
don't know why the generated code would require the first two lines within.
If I rerun code, then I generate a second copy of the worksheet change
and when I change a cell I get an message
Ambiguos name detected: Worksheet_Change
which would be expected. I am using Excel 2000.



"Les Stout" wrote in message ...
Hi Tom, I tried your new code and still get the result below, i just
cant understand it ?

"Microsoft Excel has encountered a problem and needs to close. We are
sorry for the inconvenience."

Do i perhaps have to declare references or something ?

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi Dave, as per my previous thread it seems ok now thanks for your
input.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Target is just a range reference to the cell that was changed. So you would
use the properties of the range. As you show,
Target.font.colorIndex = 37
would be correct.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
Hi Tom, i tried it with stepping into it with F8 and then F5 and it runs
perfectly, it is when i try and step through the proceedure with F8 ?
If i would like to change the font color instead of the background,
would i then change it in the following way

Target.Interior.ColorIndex = 37

to

Target.Font.ColorIndex = 37


Thanks a million again for your patience and assistance.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Hi, sorry guys one last question now that that is sorted. I see that you
can just change the color back to normal with the menu. Is it possible
to lock this so that the user cannot change it back or remove the color
? I can take away the menu bar and toolbars, but do not know if that is
the best way ?

Thanks.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

If the cells are unlocked and the sheet protected, then the user can not
change the format through the normal menu, but could by copying and pasting.

If your users are hostile and bent on not cooperating, then I am not sure
you have a lot of options.

--
Regards,
Tom Ogilvy

"Les Stout" wrote in message
...
Hi, sorry guys one last question now that that is sorted. I see that you
can just change the color back to normal with the menu. Is it possible
to lock this so that the user cannot change it back or remove the color
? I can take away the menu bar and toolbars, but do not know if that is
the best way ?

Thanks.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBE Help
 

Thanks Tom, i really appreciate your help. On my way home now, 8 at
night here cheers..

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***