D
dustinb via OfficeKB.com
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.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=1, Top:=1, Width:=135, Height:= _
25.5).Select
ActiveSheet.OLEObjects("CommandButton1").Object.cmdButton.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.".
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.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=1, Top:=1, Width:=135, Height:= _
25.5).Select
ActiveSheet.OLEObjects("CommandButton1").Object.cmdButton.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.".