I wrote a simple Worksheet Event for a user so that the font color of
any cell she alters in an existing worksheet will be changed to red:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Cells) Is Nothing Then
Selection.Font.ColorIndex = 3
End If
End Sub


Since she will be reusing this code periodically, I copied it to a
text file which I placed on her desktop. The instructions I gave her
for whenever she wants to use this code are simply to open the text
file, Copy the text, right-click on the worksheet tab, choose View
Code from the menu, do Paste to copy in the code, then exit the VBE.

While these instructions are relatively simple, she has asked me for
an automated method. ("Can't you just give me a button to click?")

Accordingly, I'd like to put a macro in her Personal.xls and assign it
to a custom toolbar button so that all she has to do is click the
button to apply the event to whatever worksheet is active at the time.

This needs to be a Worksheet Event rather than a Workbook Event since
she will only be using this on selected worksheet(s) in multiple-sheet
workbooks. Searching this newsgroup, I have only found references to
macros that will apply Workbook Events. I've tried modifying such code
for Worksheet Events but without success.

One of the complications is that the macro code cannot refer to the
worksheet by name since very often the sheet will be something other
than "Sheet1",

What macro code would I use to copy the event into whatever worksheet
is active at the time?

Many thanks,
Paul

Hi Bob,

Thanks very much for your response - I appreciate it very much.

Yes, this is for existing worksheets only, with the purpose of having
altered cells standout by changing their foreground color to red.

As far as Chip's excellent website, I did in fact go to it before
posting my question. However, all the examples (as far as I can tell)
refer to Workbook Events rather than Worksheet Events, and my level of
knowledge is such that I have been unable to sucessfully translate
Chip's code to apply to Worksheet Events, especially given the fact
the any code that applies the Worksheet Event cannot refer to the
sheet by name (since the name can vary) and must therefore somehow
refer to the "ActiveSheet".

But, again, I do appreciate your time and effort in trying to help me
with this matter.

Best regards,
Paul


"Bob Phillips" wrote in message ...
Paul,

I was going to give you a method of importing a file with the code in, but
on re-reading the post, I think you want to add it to existing sheets.
Therefore, I suggest you go to Chip's site and check-out
http://www.cpearson.com/excel/vbe.htm which shows you the technique. You
will need to create the toolbar button and assign the macro to it.

--

HTH

Bob Phillips

"Paul Simon" wrote in message
om...
I wrote a simple Worksheet Event for a user so that the font color of
any cell she alters in an existing worksheet will be changed to red:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Cells) Is Nothing Then
Selection.Font.ColorIndex = 3
End If
End Sub


Since she will be reusing this code periodically, I copied it to a
text file which I placed on her desktop. The instructions I gave her
for whenever she wants to use this code are simply to open the text
file, Copy the text, right-click on the worksheet tab, choose View
Code from the menu, do Paste to copy in the code, then exit the VBE.

While these instructions are relatively simple, she has asked me for
an automated method. ("Can't you just give me a button to click?")

Accordingly, I'd like to put a macro in her Personal.xls and assign it
to a custom toolbar button so that all she has to do is click the
button to apply the event to whatever worksheet is active at the time.

This needs to be a Worksheet Event rather than a Workbook Event since
she will only be using this on selected worksheet(s) in multiple-sheet
workbooks. Searching this newsgroup, I have only found references to
macros that will apply Workbook Events. I've tried modifying such code
for Worksheet Events but without success.

One of the complications is that the macro code cannot refer to the
worksheet by name since very often the sheet will be something other
than "Sheet1",

What macro code would I use to copy the event into whatever worksheet
is active at the time?

Many thanks,
Paul

Hello Dave,

Thank you very much for responding to my question (and for that
matter, for all the help you've given all of us over the past years).

You've given me some great material to work with here, and I'm sure
that between your sample code and the websites you suggested, I should
be able to work this out.

Best regards,
Paul


Dave Peterson wrote in message ...
This kind of event is called an application event. Chip Pearson has some very
nice notes at:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

(and a couple of sample workbooks, too!)

But you can try this:
Select your project in the VBE.
Insert|Class Module (probably defaulted to Class1)

Paste this into this code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
target.font.colorindex = 3
End Sub

Then in the ThisWorkbook module of that same project, paste this in:

Option Explicit
Dim myChangeClass As Class1
Private Sub Workbook_Open()
Set myChangeClass = New Class1
Set myChangeClass.xlApp = Application
End Sub
Private Sub workbook_close()
Set myChangeClass.xlApp = Nothing
End Sub

Save this workbook and either run workbook_open or close the workbook and reopen
it.

But now with every change she makes to any workbook will have a font colorindex
of 3. I think I'd make it into an addin (File|saveAs|file of type: Addin).
Store it in a nice spot.

Then Tools|Addins and browse to that file.

Tell her that when she wants to use it, to enable it via tools|Addins. When she
doesn't want it, disable it via Tools|Addins.

And notice that the Edit|Undo is lost, too. (most macros kill that when they
run.)





Paul Simon wrote:

Hi Bob,

Thanks very much for your response - I appreciate it very much.

Yes, this is for existing worksheets only, with the purpose of having
altered cells standout by changing their foreground color to red.

As far as Chip's excellent website, I did in fact go to it before
posting my question. However, all the examples (as far as I can tell)
refer to Workbook Events rather than Worksheet Events, and my level of
knowledge is such that I have been unable to sucessfully translate
Chip's code to apply to Worksheet Events, especially given the fact
the any code that applies the Worksheet Event cannot refer to the
sheet by name (since the name can vary) and must therefore somehow
refer to the "ActiveSheet".

But, again, I do appreciate your time and effort in trying to help me
with this matter.

Best regards,
Paul

"Bob Phillips" wrote in message ...
Paul,

I was going to give you a method of importing a file with the code in, but
on re-reading the post, I think you want to add it to existing sheets.
Therefore, I suggest you go to Chip's site and check-out
http://www.cpearson.com/excel/vbe.htm which shows you the technique. You
will need to create the toolbar button and assign the macro to it.

--

HTH

Bob Phillips

"Paul Simon" wrote in message
om...
I wrote a simple Worksheet Event for a user so that the font color of
any cell she alters in an existing worksheet will be changed to red:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Cells) Is Nothing Then
Selection.Font.ColorIndex = 3
End If
End Sub


Since she will be reusing this code periodically, I copied it to a
text file which I placed on her desktop. The instructions I gave her
for whenever she wants to use this code are simply to open the text
file, Copy the text, right-click on the worksheet tab, choose View
Code from the menu, do Paste to copy in the code, then exit the VBE.

While these instructions are relatively simple, she has asked me for
an automated method. ("Can't you just give me a button to click?")

Accordingly, I'd like to put a macro in her Personal.xls and assign it
to a custom toolbar button so that all she has to do is click the
button to apply the event to whatever worksheet is active at the time.

This needs to be a Worksheet Event rather than a Workbook Event since
she will only be using this on selected worksheet(s) in multiple-sheet
workbooks. Searching this newsgroup, I have only found references to
macros that will apply Workbook Events. I've tried modifying such code
for Worksheet Events but without success.

One of the complications is that the macro code cannot refer to the
worksheet by name since very often the sheet will be something other
than "Sheet1",

What macro code would I use to copy the event into whatever worksheet
is active at the time?

Many thanks,
Paul

Chip Pearson has some stuff you'll want to read:
http://www.cpearson.com/excel/vbe.htm

Dennis Hancy wrote:

I think I have a similar situation.

In part of my VBA, I create a new worksheet using the code:

worksheets.add.move befo=Worksheets(1)

What I need to do is add code to the Worksheet_Change event.

Natually I can add this "manually" after the fact, but I'd like to add
this code to the new worksheet as soon as it's created.

Can this be done?

Thanks.

Dennis Hancy
Eaton Corporation
Cleveland, OH

<<snipped
--

Dave Peterson