Help Excel 2003 code failing in 2007
 

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.

Help Excel 2003 code failing in 2007
 

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




"Bob C" wrote in message
...
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.

Help Excel 2003 code failing in 2007
 

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.


"Nigel" wrote:

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




"Bob C" wrote in message
...
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.

Help Excel 2003 code failing in 2007
 

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

Help Excel 2003 code failing in 2007
 

the browser wraps it incorrectly

should be:

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

Help Excel 2003 code failing in 2007
 

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

Help Excel 2003 code failing in 2007
 

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.


"Nigel" wrote:

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

Sorry no other suggestions

--

Regards,
Nigel




"Bob C" wrote in message
...
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.


"Nigel" wrote:

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




"Bob C" wrote in message
...
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.

Help Excel 2003 code failing in 2007
 

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

Help Excel 2003 code failing in 2007
 

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

Help Excel 2003 code failing in 2007
 

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

--
Thank you

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


"Dave Peterson" wrote:

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
--

Dave Peterson

Help Excel 2003 code failing in 2007
 

It would appear that the 33 character limitation on button text in xl2007
caused the error to happen. I have since changed the button text to 29
characters in my original code, which now runs as it did in xl2003.
Also noticed that the Length:=29 had to be one greater than actual
characters & spaces etc. of text, other wise the .ColorIndex =13 would fail.
Does Excel add an end of line character, perhaps?

--
Thank you

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

Help Excel 2003 code failing in 2007
 

I'm not sure how you changed the caption, but did you include the vblf character
(same as chr(10)) in your count of characters?

Bob C wrote:

It would appear that the 33 character limitation on button text in xl2007
caused the error to happen. I have since changed the button text to 29
characters in my original code, which now runs as it did in xl2003.
Also noticed that the Length:=29 had to be one greater than actual
characters & spaces etc. of text, other wise the .ColorIndex =13 would fail.
Does Excel add an end of line character, perhaps?

--
Thank you

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

--

Dave Peterson

Help Excel 2003 code failing in 2007
 

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.

--
Thank you

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


"Dave Peterson" wrote:

I'm not sure how you changed the caption, but did you include the vblf character
(same as chr(10)) in your count of characters?

Bob C wrote:

It would appear that the 33 character limitation on button text in xl2007
caused the error to happen. I have since changed the button text to 29
characters in my original code, which now runs as it did in xl2003.
Also noticed that the Length:=29 had to be one greater than actual
characters & spaces etc. of text, other wise the .ColorIndex =13 would fail.
Does Excel add an end of line character, perhaps?

--
Thank you

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

--

Dave Peterson