Help Excel 2003 code failing in 2007

B

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
 
N

Nigel

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

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

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.


Nigel said:
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)



Bob C said:
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.
Click to expand...
Click to expand...
 
J

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
 
J

Jarek Kujawa

the browser wraps it incorrectly

should be:

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

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



Bob said:
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.
Click to expand...
 
B

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.


Nigel said:
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)



Bob C said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
B

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.


Dave Peterson said:
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 said:
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.
Click to expand...
Click to expand...
 
D

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


Bob said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
B

Bob C

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

Dave Peterson

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

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.
 

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

Similar Threads

Showing charts with hidden data 4
Chart runtime error 1004 please help 2
Export from EXcel to jpg with 2007 4
Problem with excel macro - getting automation error - URGENT 1
Need for categories to be displayed in chart 3
manipulating drawing objects in MS Office 2007 2
Ron de Bruin - Merge cells from all worksheets 0
2003 vs 2007 Shape Selection Error 3

Top