I have a spreadsheet that goes through some data and organizes it into two
other sheets within the same workbook. The original sheet is left as is
because it contains more detail than I have pulled out into the two new
sheets. I am trying to build some buttons into the code and can't figure out
why I can't change the caption on the buttons. I have made on click events
that will correspond to names that I will assign to the buttons. The names
part works and the buttons function but the caption still reads
CommandButton1. I have tried several versions of the below

Workbooks("WCM Macro.xls").Activate
Worksheets("Unformatted Report").Activate
Range("A1").Select

Selection.EntireRow.Insert ' Moves the data down a couple of lines so the
button is not over top of text.
Selection.EntireRow.Insert

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=1, Top:=1, Width:=135, Height:= _
25.5).Select

ActiveSheet.OLEObjects("CommandButton1").Object.cm dButton.Caption = "Track
Selected Cell"

With ActiveSheet.Shapes("CommandButton1")
.Name = "cmdTrackShipment"
.Caption = "Track Selected Cell"
End With


Also have tried the below for caption outside of the with statement, after
commenting out the current caption line.

ActiveSheet.Shapes("CommandButton1").Caption = "Track Selected Cell" ' Before
I rename the button.

Dim cmdTrackShipment as Object ' Tried object and shape.

With cmdTrackShipment
.Caption = "Track Selected Cell"
End With

Several other options have been tried to no avail. Most of the time I get an
error that says "Object doesn't support this property or method.".

--
Message posted via http://www.officekb.com

You had too much in the statement. Try this.

ActiveSheet.OLEObjects("CommandButton1").Object _
..Caption = "Track Selected Cell"


"dustinb via OfficeKB.com" wrote:

I have a spreadsheet that goes through some data and organizes it into two
other sheets within the same workbook. The original sheet is left as is
because it contains more detail than I have pulled out into the two new
sheets. I am trying to build some buttons into the code and can't figure out
why I can't change the caption on the buttons. I have made on click events
that will correspond to names that I will assign to the buttons. The names
part works and the buttons function but the caption still reads
CommandButton1. I have tried several versions of the below

Workbooks("WCM Macro.xls").Activate
Worksheets("Unformatted Report").Activate
Range("A1").Select

Selection.EntireRow.Insert ' Moves the data down a couple of lines so the
button is not over top of text.
Selection.EntireRow.Insert

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=1, Top:=1, Width:=135, Height:= _
25.5).Select

ActiveSheet.OLEObjects("CommandButton1").Object.cm dButton.Caption = "Track
Selected Cell"

With ActiveSheet.Shapes("CommandButton1")
.Name = "cmdTrackShipment"
.Caption = "Track Selected Cell"
End With


Also have tried the below for caption outside of the with statement, after
commenting out the current caption line.

ActiveSheet.Shapes("CommandButton1").Caption = "Track Selected Cell" ' Before
I rename the button.

Dim cmdTrackShipment as Object ' Tried object and shape.

With cmdTrackShipment
.Caption = "Track Selected Cell"
End With

Several other options have been tried to no avail. Most of the time I get an
error that says "Object doesn't support this property or method.".

--
Message posted via http://www.officekb.com

Great !!!! Thanks for the help.
JLGWhiz wrote:
You had too much in the statement. Try this.

ActiveSheet.OLEObjects("CommandButton1").Object _
.Caption = "Track Selected Cell"

I have a spreadsheet that goes through some data and organizes it into two
other sheets within the same workbook. The original sheet is left as is
[quoted text clipped - 39 lines]
Several other options have been tried to no avail. Most of the time I get an
error that says "Object doesn't support this property or method.".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1