Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
worksheet function in event macro | Excel Worksheet Functions | |||
macro apply to all worksheet | Excel Worksheet Functions | |||
Delete a Column when in a Macro-Worksheet Event? | Excel Worksheet Functions | |||
Event macro that targets specific worksheet | Excel Discussion (Misc queries) | |||
Not apply macro to every worksheet in activeworkbook | Setting up and Configuration of Excel |