Command Button Caption - VBA

  • Thread starter dustinb via OfficeKB.com
  • Start date
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.".
 
J

JLGWhiz

You had too much in the statement. Try this.

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

dustinb via OfficeKB.com

Great !!!! Thanks for the help.
JLGWhiz said:
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.".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top