1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Error in Excel 2007 but not in Excel 2003

Discussion in 'Business Applications' started by N2LifeSC, Oct 26, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. N2LifeSC

    N2LifeSC Thread Starter

    Joined:
    Jun 22, 2006
    Messages:
    3
    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 defined as: Public CurrentJob As String

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

    No problem at all in Excel 2003 and earlier. If CurrentJob is something shorter (e.g. MyFile), there is no error. I don't think the spaces are a problem either because I still got the error after adding Chr(34) before and after CurrentJob in my code. Any ideas? Thank you.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hello, welcome to the board!

    The text is too long. Use an ActiveX control instead of a Forms control. Then you can use similar code...

    Code:
    Public Const strCurrentJob As String = "RockMont Rev 7-9-04"
    Dim btn As CommandButton
    
    Sub FooTest()
        Set btn = Sheet1.CommandButton1
        btn.Caption = "Prepare Job Summary" & Chr(10) & "for " & strCurrentJob
    End Sub
    HTH
     
  3. N2LifeSC

    N2LifeSC Thread Starter

    Joined:
    Jun 22, 2006
    Messages:
    3
    Thank you for the response. Do you know why the length of the text is prohibitive? Did the length of text you can display on a button (a form control) change between Excel 2003 and Excel 2007? The variable CurrentJob can take on several different values, depending on which file of data is being processed.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I don't know. I've never seen anything officially published about this. But, as Forms controls are trying to be phased out, it would make sense that functionality would eventually be hindered. You should go for ActiveX controls anyway.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/643691

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice