Excel CommandButton Changes with VBA 1

[jclarkpowereng]

I am adding a commandbutton to a Excel worksheet with the following command:

OutputSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=150, Top:=1330, Width:=200, Height:=50).Select

It adds the button and names it "CommandButton1".

I want to rename that button (the caption that appears on the button face)
and assign a macro to the button that will run when someone pushes the
button - and am having a hard time doing that with code.

Can you offer any suggestions?

 

[pragmatic]

Here's some code from one of my older applications...

ActiveSheet.Buttons.Add(450.75, 48.75, 77.25, 32.25).Select
Selection.OnAction = "clliar"
ActiveSheet.Shapes.SelectAll
Selection.Characters.Text = "AutoRevert"
With Selection.Characters(start:=1, Length:=4).Font
.Name = "arial"
.FontStyle = "regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = False
.ColorIndex = False
End With
With Selection
.Placement = xlFreeFloating
.PrintObject = False
End With

The above code adds the button, assigns the macro, and adds the caption or text to the button, as well as some other properties. It's kind of rough looking, but it works, I've never had the need to clean the code up. By the way, I think I origninally just recorded adding a button, to come up with the original code.

 

[jclarkpowereng]

Thank you, but when I use that snippet, I get a "Run-time error '1004': Unable to set the Text property of the Characters class".

Debug stops at: Selection.Characters.Text = "My Text Here"

I am using version 6 of Excel VB.

Also, I was inserting a CommandButton which seems to behave differently. The Button you're having me insert is a little different but it would certainly work.

Thanks - any ideas on the error?

 

[jclarkpowereng]

Oh yah, and I'm actually trying to assign a section of code (small subroutine) to this button - not actually a macro - just to make things more difficult. Sorry

 

[RobBroekhuis]

There is no difference between a macro and a subroutine, as far as VBA is concerned. Did pragmatic's answer help?


Rob

 

[pragmatic]

Are there other objects (i.e. command buttons), you may want to leave off the following section of code:

ActiveSheet.Shapes.SelectAll

That was a hold over from recording the button/macro.

You should be able to assign the sub module the same as a macro...

 

[duncansancho]

The command
ActiveSheet.Shapes.SelectAll
must refer to the button created and not to all objects since they cannot all be renamed at the same time.
eg.
ActiveSheet.Shapes("Button 3&quot.Select
The problem then is how to refer to the button since the number will vary depending how often the routine is used.

 

[duncansancho]

Try the code as shown by pragmatic, but leave out the line
ActiveSheet.Shapes.SelectAll
altogether - it seems to work OK for me

 

[jclarkpowereng]

That got me where I needed to go. Thank you to all.