Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a COM-AddIn for Excel (2002/1)

    We have nearly 200 Excel templates whose "thisworkbook" code is the same for each template. We would like to use a COM AddIn to handle the functions now handled by the thisworkbook that is in each template. How does one hook up the various Workbook events that can exist in the stardand thisworkbook to be in the Com Addin instead?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    You need to create application-level event handlers for this. Do a search for WithEvents in this forum and you'll find instructions and examples.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    We set up our Com Addin with code to use WithEvents associated with a workbook object so that it would house the same events and code that the template's object "ThisWorkbook" that we wish to leave empty would have, but the events are not being found. We have seen various samples of the use of WithEvents but nothing that is trying to do what we are which is to leave the default template object "ThisWorkbook" empty and instead use a COM addin to catch the events that normally would go to the default template object "ThisWorkbook". Since the default template object "ThisWorkbook" cannot be removed I am wondering if what we are trying to do is even possible.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    I'm afraid I can't help you there, but I'm sure other Loungers will jump in.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    Well I may have foudn a good hint via a reply you gave to someone else in 2001 titled"Making an code in an Event available everywhere (Excel 97 SR2)". Here is your reply to that other person:

    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)



    Private Sub xlAppFormat_SheetChange(ByVal Sh As Object, ByVal Target As Range) Target.Font.Color = vbRed Target.Font.Bold = TrueEnd Sub

    Now insert a standard module. Enter the following code:



    Public xlApplication As New FormatClsSub TrapAppFormatEvent() Set xlApplication.xlAppFormat = ApplicationEnd Sub

    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:



    Private Sub Workbook_Open() Call TrapAppFormatEventEnd Sub

    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.

    HP

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    I can't claim that post - I joined the Lounge in March of 2002. The reply you mention is by another Hans: <!profile=Hans Pottel>Hans Pottel<!/profile>.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    Well I was trying to give you credit [img]/forums/images/smilies/smile.gif[/img]. Did you happen to read that Han's reply and if so does it sound like that is simular to what I described?

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using a COM-AddIn for Excel (2002/1)

    Here is the code that I used when testing whether this could be done:

    Class Module, slWorksheet.cls --
    <pre>Option Explicit

    Public WithEvents xlWorkSheet As Excel.Worksheet

    Private Sub xlWorkSheet_BeforeDoubleClick(ByVal Target As Excel.Range, _
    Cancel As Boolean)
    MsgBox "Dbl-click on " & xlWorkSheet.Name
    End Sub</pre>

    Designer module, OnConnection (left out OnDisconnection, it needs to set all objects to Nothing)--
    <pre>Option Explicit

    Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
    Set xlApp = Application
    Set xlBook = xlApp.ActiveWorkbook
    CreateToolbar
    EnableWorksheets
    SummaryButton
    MsgBox App.ProductName & _
    " V" & App.Major & "." & App.Minor & "." & App.Revision & _
    " connected to " & xlApp.Name
    End Sub</pre>

    Code module called by OnConnect --
    <pre>Option Explicit
    Dim ezWS() As New slWorksheet

    Sub EnableWorksheets()
    Dim i As Integer, n As Integer
    Dim s As Excel.Worksheet
    n = xlBook.Worksheets.Count
    ReDim ezWS(1 To n)
    For i = 1 To n
    Set ezWS(i).xlWorkSheet = xlBook.Worksheets(i)
    Next i
    End Sub</pre>


    This is as far as I have tested worksheet events in a COM addin. I believe that I also discovered that the EnableEvents property of the Excel application does not stop these "class" events. This was a big problem for me, but I decided that I could delete all of the slWorksheet objects to disable interrupts and then recreate then to enable interrupts. Haven't tested this idea yet. Hope this helps! If you get a COM addin working, you deserve a big raise. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using a COM-AddIn for Excel (2002/1)

    P.S., don't feel bad about confusing HansP with HansV. When HansV became an MVP, I sent HansP a congratulation message! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> HansP has been an MVP forever. Ah well, they're both geniuses!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    Thanks. I will give your idea a try as well and let you know.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    That doesn't work either.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using a COM-AddIn for Excel (2002/1)

    Jim,
    Does the attached give you any help? It's very simplistic with only one workbook event trapped but hopefully it will give you the idea. (I've included the .vba file and the compiled .dll)
    HTH.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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