Help Excel 2003 code failing in 2007

[Bob C]

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

 

[Nigel]

It appears that the Chr(10) is the issue here, try using

Selection.Characters.Text = "Click to Run" & vbCrLf & _
"all macro's automatically."

 

[Bob C]

Hi Nigel

It stopped on the same line with the following error message;

'Run Time Error '1004'
'Unable to set text property of the characters class'

Thank you

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

It appears that the Chr(10) is the issue here, try using

Selection.Characters.Text = "Click to Run" & vbCrLf & _
"all macro's automatically."


--

Regards,
Nigel
(e-mail address removed)

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.

 

[Jarek Kujawa]

I copied the row to my PC and it reads

Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's "
automatically."

delete the rightmost " sign in the 1st row and adjust all to look like
this:

Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's
automatically."

it works on my Excel 2007

 

[Jarek Kujawa]

the browser wraps it incorrectly

should be:

Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's _
automatically."

 

[Dave Peterson]

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

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.

 

[Bob C]

Sorry about late reply Nigel just woke up here in Australia.
Thank you for you time & effort.
I hope Dave Peterson's post will fix the problem.
--
Thank you

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

You original code did not work for me in xl2007; my modified code did work
ok !!

Sorry no other suggestions

--

Regards,
Nigel
(e-mail address removed)

Hi Nigel

It stopped on the same line with the following error message;

'Run Time Error '1004'
'Unable to set text property of the characters class'

Thank you

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

It appears that the Chr(10) is the issue here, try using

Selection.Characters.Text = "Click to Run" & vbCrLf & _
"all macro's automatically."


--

Regards,
Nigel
(e-mail address removed)



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.

 

[Bob C]

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.

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

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]

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

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.

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

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.

 

[Bob C]

Dave, I reduced the button text to 33 characters and it works perfectly.

 

[Bob C]

I used my original code with less characters in the text.

You're correct, I didn't include the non visible chr(10) in my character
count.
In the back of my mind I had the feeling I'd struck this character count
problem before when entering two lines of text onto a button face.