CommandButtons via Visual Basic 6.0

From Ashton. When I run this code in a VB6 program, it puts an OLEObject on an Excel sheet.

Private Sub Command1_Click()
   
    Dim xlApp As Excel.Application
   
    Set xlApp = Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Add
   
    xlApp.ActiveSheet.OLEObjects.Add “Forms.Commandbutton.1”
   
    Set xlApp = Nothing
   
End Sub

But in Excel, it doesn’t act like an OLEObject. Look at these context menus.

The first one was put there by the VB6 program. I added the second one manually from the Control Toolbox in Excel.

Show Picture toolbar button? What the heck is that? Does anyone know why there’s a difference?

Posted in Uncategorized

8 thoughts on “CommandButtons via Visual Basic 6.0

  1. Hi!

    I’m using this method to add buttons all the time. The two buttons are the same type of object. If you bring up the “Properties” dialog, you’ll see that the two buttons have the exact same properties.

    Why Excel displays a different context menu I don’t know..

  2. Jan Karel –

    I see that great minds think alike. Although you responded from memory, while I tested it just now out of curiosity. That must account for the extra 4 minutes.

  3. The two popup menus are,

    Controls added by code, either within VBA or VB, get treated as OLE Objects and use the following menu,
    Application.CommandBars(“OLE Object”).ShowPopup

    Controls added manually use the activex control menu,
    Application.CommandBars(“ActiveX Control”).ShowPopup

    The manual task appears to be setting a property to enable Excel to distinguish between the 2, but I have yet to discover what that is.

  4. From: JohnDow@shortsalebuyer.net : I have the same problem that the undersigned has. The only difference is that I can not interpret the answer/formula as I am not familiar with V. B.; thus the final outcome is failure. Any possible chance that I could e-mail you or ??? an Excel Spread sheet of six cells as an attachment and ??? could send me back the results I am looking for?

    > I have copied a web page into EXCEL that contains numerous hyperlinks
    > labeled “e-mail”. When I click on the hyperlink Outlook opens and
    > the e-mail address is populated on the “to:” line. I want copy the
    > underlying e-mail address in the adjacent cell, but can not find a
    > way to do it (all I get is another cell labeled “e-mail”). I can do
    > it by right clicking on the cell and selecting “edit hyperlink” and
    > do a copy/paste w/ the e-mail address. However, I have a ton to do
    > and was hoping to find a quick way to copy the email address, Thanks
    > for your help.

    You can use this UDF I found on http://www.ozgrid.com:

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace _
    (HyperlinkCell.Hyperlinks(1).Address, “mailto:”, “”)
    End Function


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *