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
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?
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..
IIRC the same happens when you add the button from Excel VBA. Odd quirk.
Running the code in VBA gives you the same context menu as running it in VB6. FWIW.
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.
What’s even more interesting is the “Asign Macro” selection usually found in Form buttons (not ActiveX buttons).
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.
Funny thing. I only get it on 2003. There is no difference in excel 2007.
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