Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making an code in an Event available everywhere (Excel 97 SR2)

    Hiya...
    First timer here, so forgive me if I make a faux pas...

    I have working code that formats cells in a given way when the user leaves the cell (ENTER, TAB, Click, etc). I wrote it in the Worksheet_Change event.
    However, I need this code to be accessible in any worksheet... So I tried putting it into PERSONAL.XLS, but I can't seem to figure out where it belongs...

    Any help would be appreciated.

    <font color=blue>Jason</font color=blue>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    You would put it in Personal.xls in a standard module. You would then call it from the Change Event routine in the sheets where it was needed.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    So, there is no way to make it automatically inherited to any workbook? I have to add code to an event in the workbook I want it to be used in?
    Is there some other way to do it?

    <font color=blue>Jason</font color=blue>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    Not that I know of. Excel is only going to execute the event routine in the workbook that caused the event. There has to be code there.

    If you want all future workbooks created by this Excel to contain the code in its event routine, you might be able to put the code in the event routine in the Book.xlt or Sheet.xlt templates. However, this will not affect any workbooks that have already been created.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    What about the method used here:
    mk:@MSITStore:C:Program FilesMicrosoft OfficeOffice1033VBAXL9.CHM::/html/xlhowUsingAppEvents.htm (of course, your actual path may vary - you should be able to find it by searching Excel VBA Help for "Using Events with the Application Object")?

    This seems to be what I want to do, but I can't get it to work...

    <font color=blue>Jason</font color=blue>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    Sorry, I can not seem to find what you are pointing to.
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    Two alternatives:

    a) Add the necessary modules/code to the target workbook at runtime:
    <A target="_blank" HREF=http://www.cpearson.com/excel/vbe.htm>http://www.cpearson.com/excel/vbe.htm</A>

    [img]/forums/images/smilies/cool.gif[/img] Define it as template

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making an code in an Event available everywhere (Excel 97 SR2)

    Jason,

    As you already figured out, it is possible to do what you want by using so-called Application events. I'll try to guide you through an example that will change the font of all entries into bold and red color.

    First you need to add a class module. Give it a name. I called my class module FormatCls. The name is important as you will use it in your code. Now add the following public declaration to the class module:

    Public WithEvents xlAppFormat As Application

    The "xlAppFormat" can be whatever name you want. When you have entered that line, the xlAppFormat can now be choosen from the left hand side drop down box at the top of the module and you can use the right hand side drop down box to select the event you want. Select the SheetChange event and enter the code you want to format your entries (as an example you can see my code below)

    <pre>Private Sub xlAppFormat_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Target.Font.Color = vbRed
    Target.Font.Bold = True
    End Sub
    </pre>


    Now insert a standard module. Enter the following code:

    <pre>Public xlApplication As New FormatCls

    Sub TrapAppFormatEvent()
    Set xlApplication.xlAppFormat = Application
    End Sub
    </pre>


    Unlike sheet and workbook events, the event procedures you place in your own class modules do not automatically function. You need to create an instance of your class module and assign the Application object to the xlAppFormat property of the new object.

    All you need to do now is execute the TrapAppFormatEvent procedure. You can do this by adding this call to the Workbook_Open event:

    <pre>Private Sub Workbook_Open()
    Call TrapAppFormatEvent
    End Sub
    </pre>


    That's all it is. As simple as that.
    If you want to enable application event processing for all Excel sessions, you can place your class module and standard module code in the Personal.xls and execute the TrapAppFormatEvent from the Workbook_Open event, or you can make an add-in of it.

    Hope this is what you were looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •