macro to apply worksheet event to active worksheet

  • Thread starter Thread starter Paul Simon
  • Start date Start date
P

Paul Simon

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
 
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
 
Chip Pearson has some stuff you'll want to read:
http://www.cpearson.com/excel/vbe.htm

Dennis said:
I think I have a similar situation.

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

worksheets.add.move before:=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
Click to expand...
<<snipped>>
 
Back
Top