Code produces error in Excel 2007 but not in Excel 2003

[N2Life]

Why does the second line of the code shown below produce an error in Excel 2007 and not in Excel 2003? The error is:
Run Time Error ‘1004’, Unable to set the Text Property of the Characters class

ActiveSheet.Shapes("btnJobSummary").Select
Selection.Characters.Text = "Prepare Job Summary" & Chr(10) & "for " & CurrentJob

CurrentJob is: Public CurrentJob As String

In this case it equals: RockMont Rev 7-9-04

No problem at all in Excel 2003 and earlier. I have tried adding Chr(34) before and after the CurrentJob, but get same error. Any ideas? Thank you.

 

[Pcfred]

I had similar issues and it ended up being the worksheet/workbook protection. If you have protected you sheet, try removing all protection to see if it has anything to do with your issue.

 

[N2Life]

Thank you PCfred. No sheets in the workbook are protected. I should have added that when the CurrentJob is something shorter (example: MyJob), the error does not occur. I can't see how the length would cause a problem. I don't have Excel 2007 (I consider myself fortunate in that respect), but need to solve this problem for a customer who has recently upgraded.

 

[HughLerwill]

Have you tried using the Macro recorder to do something similar under Excel 2007 to see how the code turns out?

 

[N2Life]

I'll try the Macro recorder next time I am around Excel 2007, but I'm not sure that is possible since I am using the Chr(10) concatenated with a variable. It's not the kind of thing you usually record.

 

[xlbo]

What happens when you remove the Chr(10) part of the string? Does it still error? If so, sounds like it is the length of the text, if not it is character definition of chr(10)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ck1999]

Have you tried Chr(13)? also what type of shape are you using. I have excel 2007 and was trying to reproduce the error.

ck1999

 

[HughLerwill]

A brace of macros recorded on Excel 2007;

Sub Macro1()
'nl remmed after recording
'ActiveSheet.Buttons.Add(258.75, 48.75, 180, 33.75).Select
Dim currentjob As String
currentjob = "RockMont Rev 7-9-04"

ActiveSheet.Shapes("Button 1").Select
'nl remmed after recording
'Selection.Characters.Text = "My " & Chr(10) & "Button" & Chr(10) & ""
'nl inserted after recording
Selection.Characters.Text = "Prepare Job Summary " & Chr(10) & "for " & currentjob & Chr(10) & "" 'crashes on this line if currentjob has more than 6 characters (ie len of "Button")

With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("H12").Select
End Sub
Sub Macro2()
ActiveSheet.Buttons.Add(401.25, 132, 259.5, 34.5).Select
ActiveSheet.Shapes("Button 2").Select
Selection.Characters.Text = "Prepare Job Summary" & Chr(10) & "for RockMont Rev 7-9-04" & Chr(10) & ""
With Selection.Characters(Start:=1, Length:=44).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("L17").Select
End Sub

Note differences in 'With Selection.Characters.... line.

 

[N2Life]

My thanks to everyone. Yes, I agree that the length of what I am trying to put on that button is probably the source of the problem in Excel 2007. I just don't understand why that is. The Chr(10) doesn't give any trouble. It was suggested earlier that I switch over to ActiveX controls, so I started off on that adventure. Quickly got tired of all that effort and decided to abandon changing the text on any of the buttons. I will simply post my info for the user somewhere in big type at the top of the screen, and just pop up a message if a button is clicked on at an inopportune time. Surely Excel 2007 will not choke when I try to put that same text in a cell. The simple way is often the best and easiest way.

 

[firefytr]

I'm trying to remember which board this was posted to.. I can't remember. Anyway, I'm thinking it was a 'loose end' tied up in Excel 2007 with the text limit. Like I told you before, I'd recommend using ActiveX controls where you will not have said imposed limit. I believe I even posted an example for you.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[dgillz]

Make sure the customer has automatic updates for office 2007 turned on. If this is a bug surely it has been patched by now, even if we aren't all rushing to embrace excel 2007.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[firefytr]

No, it hasn't been 'patched by now'. There are no service releases to date for Office 2007. Microsoft is working on the first Service Pack for it, however it has yet to be released.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ck1999]

This works for me:

Using the above macro1

Sub Macro1()
'nl remmed after recording
'ActiveSheet.Buttons.Add(258.75, 48.75, 180, 33.75).Select
Dim currentjob As String
currentjob = "RockMont Rev 7-9-04"

ActiveSheet.Shapes("Button 1").Select
'nl remmed after recording
'Selection.Characters.Text = "My " & Chr(10) & "Button" & Chr(10) & ""
'nl inserted after recording
INIT = "Prepare Job Summary " & Chr(10) & "for "
LEN1 = Len(INIT)
Selection.Characters.Text = INIT 'crashes on this line if currentjob has more than 6 characters (ie len of "Button")
Selection.Characters(LEN1).Text = Chr(10) & currentjob
Range("H12").Select
End Sub

Read this in and then tried to make sure it worked

http://support.microsoft.com/kb/213841

ck1999

 

[N2Life]

I will try the suggestion of putting the length of the new button label in parentheses right after Selection.Characters. Have to send a copy to the customer to try since I don't own Excel 2007.

 

[firefytr]

Are you against using ActiveX controls as suggested?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[N2Life]

firefytr, since you asked, no, I am not a big fan of ActiveX controls. And I believe that even Excel 2007 still supports the older type of command buttons, so I see no reason to abandon something that has worked extremely well for years.

 

[ck1999]

I have excel 2007 and it worked when I tried it

ck1999

 

[firefytr]

Well, I pondered replying to you, just for the sole fact that I wasn't sure if it was actually worth it for me to invest my time in a futile struggle. The fact is technology changes and we must change with it. Why you ask? Sure it has worked before, and sure it will continue to work in the current state of things. But these things do not stay constant, they are ever changing.

Why should we move on to Windows XP? Wasn't Windows 3.1 better? What about DOS? DOS was good, and things worked perfectly well there too. I remember being perfectly content with 256 MB of hard drive space and not ever needing any more. What about VBA? Will we ever need to know more than that? It does exactly what I need it to.

The fact is, things change. DOS will not always be, just as VBA will not always be. Legacy controls such as forms buttons are being phased out for more versatile controls such as ActiveX controls. With the ActiveX controls we can trap events and manipulate them much more easily via code. And I believe this specific text 'wall' you're running up against is only going to be the first of many problems you see with using Forms controls, especially if you decide to upgrade (when MS finally stops supporting them).

Forms controls are good in their place, don't get me wrong, they can be nice. As they are made to be placed directly onto a worksheet, they can be much simpler to manipulate for the user and are easily 'assigned a macro'. But that is the only real event they have, the onaction. When clicked the assigned macro fires (via onaction). One event can be limiting and often times a contributing factor in deciding to use them or not. There have been issues raised in the past about linking a dynamic range to a forms control (i.e. combobox, listbox) and running Excel efficiently, because the forms control will update the source data with each change in the worksheet calculation [re-compile]. This is not the case with ActiveX controls.

Another note is with MS phasing out certain things, Mac support is one of those 'things'. ActiveX controls are not supported on Mac's, whereas Forms controls are. Cross-platform programming may indeed dictate the use of Forms controls (although I generally opt for dynamic worksheets and userforms if I need to do that).

Here are some related articles regarding the differences - and opinions - of both Forms vs. ActiveX controls...

http://www.dailydoseofexcel.com/archives/2004/08/11/controls-activex-vs-forms/

http://www.excelguru.ca/blog/2007/03/04/forms-controls/

http://www.ozgrid.com/Excel/Excel-controls-forms-toolbox.htm

Again, this is my opinion. You're going to do what you're going to do. But from this guy, I still suggest the ActiveX controls.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci