Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COM Addin (vb 6)

    I'm exploring the use of a COM Addin created in VB6.
    The following code works well for creating/implementing
    Excel CommandBarButton objects. - (MenuBar buttons)

    I would like to create something similar and attach the code
    to a Command Button - either a Forms button or a Control Toolbox
    Button. Any help? Thanks
    I would like to

    <pre>Option Explicit

    Dim oXL As Object
    Dim WithEvents MyButton As Office.CommandBarButton
    Dim WithEvents MyButton2 As Office.CommandBarButton


    Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
    On Error Resume Next
    MsgBox "My Addin started in " & Application.Name

    Set oXL = Application
    'delete old buttons if there persist from a crash
    On Error Resume Next
    oXL.CommandBars("Standard").Controls("My Custom Button").Delete
    oXL.CommandBars("Standard").Controls("My Custom Button2").Delete
    On Error GoTo 0

    Set MyButton = oXL.CommandBars("Standard").Controls.Add(1)
    With MyButton
    .Caption = "My Custom Button"
    .Style = msoButtonCaption

    ' The following items are optional, but recommended.
    ' The Tag property lets you quickly find the control
    ' and helps MSO keep track of it when there is more than
    ' one application window visible. The property is required
    ' by some Office applications and should be provided.

    .Tag = "My Custom Button"

    ' The OnAction property is optional but recommended.
    ' It should be set to the ProgID of the add-in, such that if
    ' the add-in is not loaded when a user presses the button,
    ' MSO loads the add-in automatically and then raises
    ' the Click event for the add-in to handle.

    .OnAction = "!<" & AddInInst.ProgId & ">"

    .Visible = True
    End With

    Set MyButton2 = oXL.CommandBars("Standard").Controls.Add(1)
    With MyButton2
    .Caption = "Color a Range"
    .Style = msoButtonCaption
    .Tag = "My Custom Button2"
    .OnAction = "!<" & AddInInst.ProgId & ">"
    .Visible = True
    End With


    End Sub

    Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
    AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    On Error Resume Next
    MsgBox "My Addin was disconnected by " & _
    IIf(RemoveMode = ext_dm_HostShutdown, _
    "Excel shutdown.", "end user.")

    MyButton.Delete
    MyButton2.Delete
    Set MyButton = Nothing
    Set MyButton2 = Nothing
    Set oXL = Nothing
    End Sub

    Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)
    MsgBox "Our CommandBar button was pressed!"

    End Sub

    Private Sub MyButton2_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)
    oXL.ActiveSheet.Range("C1015").Interior.ColorIndex = 3

    End Sub</pre>


  2. #2
    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: COM Addin (vb 6)

    Hi Paul,
    Where do you want the buttons to go? Bear in mind, that the workbook they are put into (assuming that's what you want) can be saved and closed whilst your addin is open at which point the buttons will be disconnected - unless you wanted to add code to the workbook at the same time?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COM Addin (vb 6)

    Rory,
    The buttons would go in the Workbook - same as in the above code - except they would be on a worksheet instead of in a Menu at the top. These buttons would be created at runtime , as above, and need to be deleted at closing, via more code in the COM Addin OnDisconnect. I would like the code for each button to also be located in the COM Addin.

    To get around the user closing the wbk before the Addin, for instance, I usually hide or disable the normal methods, but include a custom worksheet button in the upper right corner labeled *EXIT* This button calls the shutdown procedure that handles all necessary items to restore Excel and close in the proper order. I do this with standard Excel Addins. Now I'm exploring how to do this with a COM Addin.

    HTH

  4. #4
    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: COM Addin (vb 6)

    You should be able to declare the variables as Public WithEvents Button1 as MSForms.CommandButton and trap the Click event, I would think. Failing that, have a public routine in your add-in and simply assign the OnAction property when you create the commandbuttons.
    You could also have a workbook variable declared WithEvents, set it to the activeworkbook and trap the BeforeClose event to remove the buttons.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COM Addin (vb 6)

    Rory,
    I appreciate the help, but still can't make it work. Here is what I have so far:

    <pre>Option Explicit

    Dim oXL As Object
    Dim WithEvents MyButton As Office.CommandBarButton
    Dim WithEvents MyButton2 As Office.CommandBarButton
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
    'Declare Worksheet Command buttons:
    Public WithEvents MyButton3 As OLEObject
    Public WithEvents MyButton4 As MSForms.CommandButton

    Public MyButton5 As MSForms.CommandButton

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
    Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)

    On Error Resume Next
    MsgBox "My Addin started in " & Application.Name

    'Save a reference to Excel
    Set oXL = Application

    'delete old buttons if there persist from a crash
    On Error Resume Next
    '.....Delete buttons code goes here
    On Error GoTo 0

    Set MyButton = oXL.CommandBars("Standard").Controls.Add(1)
    '..............Add First Menu Button Code

    Set MyButton2 = oXL.CommandBars("Standard").Controls.Add(1)
    '..............Add Second Menu Button Code
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
    '' TRY ADDING WORKSHEET COMMAND BUTTONS:
    ' MyButton3 is the Control Toolbox type
    ' Mybutton4 is the MSForms type
    ' Mybutton5 is the MSForms type

    'NOTE: All 3 OF THE FOLLOWING CAUSE ERROR MSG WHILE EXCEL IS LOADING:
    ' "Runtime error 1004 Application-defined or Object-defined error "
    ' Then excel completes the load but neither of the above menu buttons work.
    ' This error occurs with the "Set...." statement alone - before we
    ' can get to the With... commands

    'NOTE2: I have added a Referece to Microsoft Forms 2.0 Object Library
    ' in Project>References
    ''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''
    ' Set MyButton3 = oXL.Sheets(1).OLEObjects.Add( _
    ClassType:="Forms.CommandButton.1", _
    Link:=False , DisplayAsIcon:=False, Left:=398.25, _
    Top:=70, Width:=90, Height:=24)
    ' With MyButton3
    ' .Caption = "ColorRngGreenDLL"
    ' .OnAction = "!<" & AddInInst.ProgId & ">"
    ' End With
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
    ' Set MyButton4 = oXL.Sheets(1).Buttons.Add(400.5, 31.5, 93, 25.5)
    ' With MyButton4
    ' .Characters.Text = "ColorRngGreenDLL"
    ' .OnAction = "!<" & AddInInst.ProgId & ">"
    ' End With
    ''''''''''''''''''''''''
    ' Set MyButton5 = oXL.Sheets(1).Buttons.Add(400.5, 100, 93, 25.5)
    ' With MyButton5... etc
    '''''''''''
    End Sub
    '''''''''''''''''''''''''''''''''''
    Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
    AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)


    On Error Resume Next
    MsgBox "My Addin was disconnected by " & _
    IIf(RemoveMode = ext_dm_HostShutdown, _
    "Excel shutdown.", "end user.")
    ''''''''''''''''''Delete Buttons

    Set MyButton = Nothing ' etc
    Set oXL = Nothing
    End Sub

    Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)

    MsgBox "Our CommandBar button was pressed!"
    End Sub

    Private Sub MyButton2_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)

    oXL.ActiveSheet.Range("C1015").Interior.ColorIndex = 3
    End Sub

    Private Sub MyButton3_Click()
    oXL.ActiveSheet.Range("C1015").Interior.ColorIndex = 4
    End Sub</pre>


    As an alternative, if you or someone else has an example that I could study, that would help. All of the examples that I have found seem to center around creating CommandBarButtons rather than Worksheet Buttons.
    I also tried simply creating a Wks cmdButton and assigning a macro to it as I would if the macro resided in a standard Excel Addin. Problem: referencing the COM Addin

    Thanks

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COM Addin (vb 6)

    Rory,

    I got it worked out and will post the solution for anyone following this thread:

    1. In the OnConnect portion of the COM Addin code, include the following lines:

    <pre>'Add a reference to this COMAddin that can be used by Excel
    'to call procedures located here.
    Application.COMAddIns.Item("MyCOMAddin1.Connect"). Object = Me
    'where MyCOMAddin1 is the name of your project</pre>



    Then add a Public sub to the Addin code like:

    <pre>Public Sub ColorRngGreenDLL()
    oXL.ActiveSheet.Range("C1015").Interior.ColorIndex = 4
    End Sub</pre>


    2. Save the project
    3. File>Make <projectname> dll
    4. In the workbook VBE set a reference to the DLL: Tools>Reference> Tick the name of your DLL
    5. Create a Worksheet Forms Button and assign a macro to it similar to the following.
    -- Declare an object variable as <your projectname>.<your designername>
    -- Set the obj variable to the COM OBJECT
    At this point, you will find the methods and properties of your COM addin exposed while writing code each time you use the obj var MyCom. Declaring it as PUBLIC will extend it further.

    <pre>Sub ColorItGreen()
    Dim MyCom As MyCOMAddIn1.Connect
    Set MyCom = Application.COMAddIns.Item("MyCOMAddin1.Connect"). Object
    MyCom.ColorRngGreenDLL
    End Sub</pre>



    HTH

Posting Permissions

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