I remember that this has come up before.

It appears that the max number of characters that you can add to the caption is
33. (I don't know a way around that limitation (bug????).)

So you could change your caption or maybe stick a button on different
(hidden???) sheet and just copy it from there (already formatted, too).

Option Explicit
Sub testme()

Dim OldBTN As Button
Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double
Dim myBTN As Button

Set OldBTN = ThisWorkbook.Worksheets("sheet1").Buttons("button 1")

With ActiveSheet
With .Range("B2")
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With

OldBTN.Copy
.Paste

Set myBTN = .Buttons(.Buttons.Count)

End With

With myBTN
'if you haven't assigned a macro to the
'template button, do it here
'.OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll"
.Top = dTop
.Left = dLeft
'if the button is sized correctly,
'I bet you don't need to adjust the width/height, either
'.Width = dWidth
'.Height = dHeight
.Width = 236.5
.Height = 50.5
End With

ActiveCell.Activate

End Sub


Bob C wrote:

Dave

Tried your code, no luck it still stops on the same line with the same error
message.
I'm running a mini mac & using VMware to run xl2007 on windows XP SP3.
I apologise, if I should have mentioned this first up.
The code before deletes a sheet in the workbook, create a new sheet then
enters the button.
All other lines of code work ok, it's just this one line causing the stop.

--
Thank you

Bob C
It cost''s little to carry knowledge with you.

"Dave Peterson" wrote:

xl2007 doesn't like working with .selections and shapes.

This worked for me in xl2003, but I didn't test it in xl2007:

Option Explicit
Sub testme()

Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double
Dim myBTN As Button

With ActiveSheet.Range("B2")
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With

Set myBTN = ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight)
With myBTN
.OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll"
.Placement = xlFreeFloating
.Width = 236.5
.Height = 50.5
.ShapeRange.IncrementTop 0.75
.Characters.Text = "Click to Run" & vbLf & "all macro's automatically."
With .Characters(Start:=1, Length:=39).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 12
.ColorIndex = 13
End With
With .Characters(Start:=1, Length:=12)
.Font.Size = 24
End With
End With

End Sub



Bob C wrote:

The code below ran in Windows Excel 2003 but stops in 2007 at the line
starting with;
Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's
automatically."
Could anyone point me as to why?
I have recorded the operation in 2007 and the line records the same as 2003.

Dim dWidth As Double
Dim dHeight As Double
Dim dTop As Double
Dim dLeft As Double

[B2].Activate
With ActiveCell
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
End With

ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select
Selection.OnAction = "Bttn1_RunAll"
Selection.Placement = xlFreeFloating
Selection.Width = 236.5
Selection.Height = 50.5
Selection.ShapeRange.IncrementTop 0.75
Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's
automatically."
With Selection.Characters(Start:=1, Length:=39).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 12
.ColorIndex = 13
End With
With Selection.Characters(Start:=1, Length:=12)
.Font.Size = 24
End With

--
Thank you

Aussie Bob C
It cost''s little to carry knowledge with you.

--

Dave Peterson


--

Dave Peterson