VBE Help

[Les Stout]

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

 

[Tom Ogilvy]

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.

 

[Les Stout]

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("ThisWorkbook").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

 

[Les Stout]

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").CodeModule
StartLine = .CreateEventProc("Worksheet", "Change") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub


Les Stout

 

[Tom Ogilvy]

Sub InsertProc()

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

Makes it the Change event of the the ActiveWorksheet.

 

[Les Stout]

Thanks Tom, really appreciate your help.

regards,

Les Stout

 

[Les Stout]

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).CodeModule
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

 

[Tom Ogilvy]

Sub InsertProc()

Dim StartLine As Long
sname = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(sname).CodeModule
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

 

[Les Stout]

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

 

[Tom Ogilvy]

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.

 

[Les Stout]

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

 

[Les Stout]

Just for the record i am using xl2002.

Les Stout

 

[David McRitchie]

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]

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

best regards,

Les Stout

 

[Tom Ogilvy]

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.

 

[Les Stout]

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

 

[Tom Ogilvy]

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.

 

[Les Stout]

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

Les Stout