Weird Automation Issue With Button Caption

  • Thread starter Thread starter Matthew Wieder
  • Start date Start date
M

Matthew Wieder

I have some VB code that automates Excel (2002/2003) and places an
ActiveX button on the spreadsheet and changes the button caption. All
this works as it is supposed to, however, the moment the application
terminates, the caption on the button reverts back to the default
caption! How can I make the caption permanent?

Imports Excel = Microsoft.Office.Interop.Excel

Sub Main()
Dim oWS As Excel.Worksheet
Dim oExcel As New Excel.Application
Dim oOleObj As Object
Dim oBtn As Object

oExcel = GetObject(, "Excel.Application")
oWS = oExcel.ActiveSheet()
oOleObj =
oWS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=60.75, Top:=30.75, Width:=90.75,
Height:=24).Object()
oBtn = oOleObj.Object
oBtn.Caption = "My Caption"
End Sub
 
Matthew,

I would rewrite almost all of the code.
The following code opens a brand new instance of Excel, adds a workbook and
a button to the first sheet from the "Forms" toolbar.
This way you don't worry about whether an existing instance of Excel is already
open
or whether your code applies to that instance or another one.
'------------------------------------------------------------
Sub Main()
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oShape As Excel.Shape

Set oExcel = New Excel.Application
oExcel.Visible = True 'Default is False
Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets(1)
Set oShape = oWS.Shapes.AddFormControl(xlButtonControl, 60.75, 30.75,
90.75, 24)
oShape.TextFrame.Characters.Text = "My Caption"
oShape.OnAction = "" 'Name of macro to run entered here
'do other stuff
Set oShape = Nothing
Set oWS = Nothing
'to clean everything up...
' oWB.Close savechanges:=True 'Your choice
' Set oWB = Nothing
' oExcel.Quit

'to leave the workbook open for your user...
Set oWB = Nothing
Set oExcel = Nothing
End Sub
'----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA
 
Matthew,

I omitted a line...

'to clean everything up...
' oWB.Close savechanges:=True 'Your choice
' Set oWB = Nothing
' oExcel.Quit
' Set oExcel = Nothing 'ADD THIS LINE

Regards,
Jim Cone
Jim Cone said:
Matthew,

I would rewrite almost all of the code.
The following code opens a brand new instance of Excel, adds a workbook and
a button to the first sheet from the "Forms" toolbar.
This way you don't worry about whether an existing instance of Excel is already
open
or whether your code applies to that instance or another one.
'------------------------------------------------------------
Sub Main()
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oShape As Excel.Shape

Set oExcel = New Excel.Application
oExcel.Visible = True 'Default is False
Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets(1)
Set oShape = oWS.Shapes.AddFormControl(xlButtonControl, 60.75, 30.75,
90.75, 24)
oShape.TextFrame.Characters.Text = "My Caption"
oShape.OnAction = "" 'Name of macro to run entered here
'do other stuff
Set oShape = Nothing
Set oWS = Nothing
'to clean everything up...
' oWB.Close savechanges:=True 'Your choice
' Set oWB = Nothing
' oExcel.Quit

'to leave the workbook open for your user...
Set oWB = Nothing
Set oExcel = Nothing
End Sub
'----------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA
Click to expand...

-snip-
 
Thanks for the response. Your code is different because you are using
the Forms button and not the ActiveX (Control Toolbox) button. You are
correct that there is no issue using the Forms button, but I need it to
work with the ActiveX button. We will be writing similar code for
several of the ActiveX controls because we need the functionality they
provide (such as data binding) and I'm trying to flesh out all the
issues. This behavior is very strange and I want to know why it is
occurring and what I can do to make the button retain it's caption.
thanks!
 
Hi Matthew,

I have tested this in Excel 2003. It reports the same as you has described. After my research, after the inserting, you can save the workbook
directly and re-open it. The caption of the inserted button will not be changed to the default one then. The steps is below:
1) create one workbook
2) add one button
3) save it and reopen
4) quit the VB.net applicaiton
The inserted oleoject button works as expected.

Furthermore, I have also created one sample code for you:
'Code begin ----------------------------------------------------
Dim oBtn As Object
Dim oOleObj As Object
Dim oWS As oExcel.Workbook
Dim oWSht As oExcel.Worksheet
Dim oApp As New oExcel.ApplicationClass

oApp.Visible = True
oWS = oApp.Workbooks.Add()
oWSht = oApp.ActiveSheet()
oOleObj = oWSht.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=60.75, Top:=30.75,
Width:=90.75, Height:=24).Object()
oBtn = oOleObj.Object
'oBtn.TakeFocusOnClick = True
oBtn.Caption = "My Caption"

Dim sFilename As String
'specify one path for the file storage location
sFilename = "<path>" & (New Random).Next().ToString() & ".xls"
oWS.SaveAs(sFilename)
oWS.Close()

oApp.Workbooks.Open(sFilename)

'exit this application
'then the commandbutton can work very well without
'changing back to the defatul name
'Please test this in the excel
Windows.Forms.Application.Exit()
'Code end ------------------------------------------------------

Please feel free to let me know if you have any further questions.

Best regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I also noticed that saving and re-opening works. Unfortunately that
isn't a good solution for our case since the tool we are building is to
help the user insert a button into their worksheet and it would be
unsightly to programmatically save, close and re-open the workbook every
time they add a button. I'm certain this is a bug, so perhaps it can be
reported and fixed...
 
Hi Matthew,

I have raised one bug in our database for this issue. However, since each hotfix will be tested fully, I am not sure when we will fix this issue now.
Greatly appreciate your understanding and reporting this to us!

Best regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top