Solved

"Unable to get the Characters property of the rectangle class" error in Excel 2007 VBA

Posted on 2013-11-08
2
960 Views
Last Modified: 2013-11-08
I have some code that worked fine in Excel 2003 :

' Set environment
   
    Application.ScreenUpdating = False

' Create text box
' NOTE: UserInterfaceOnly protection does not allow a shape to be added
   
    With ActiveSheet
        .Unprotect
        .Shapes.AddShape(msoShapeRectangle, BoxLeft, BoxTop, BoxWidth, BoxHeight).Select
    End With
   
    With Selection
   
' Use production order number as a name for the shape object

    .Name = PrdOrdNo
       
' Set the text sections

        CharCount = 0
        For I = 1 To 3
       
' Header text is in bold font; body in regular
            .Characters(CharCount + 1).Insert String:=HeaderText(I)
            TextCount = Len(HeaderText(I))
            .Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = True
            CharCount = CharCount + TextCount
            .Characters(CharCount + 1).Insert String:=BodyText(I)
            TextCount = Len(BodyText(I))
            .Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = False
            CharCount = CharCount + TextCount
        Next
       
' Set common font details

        With .Characters
            With .Font
                .Name = BoxFontName
                .Size = BoxFontSize
                .ColorIndex = BoxFontColour
            End With
        End With
       
' Set shape's colour

        With .ShapeRange
            With .Fill
                .ForeColor.RGB = RGB(BoxRed, BoxGreen, BoxBlue)
                .Transparency = 0
            End With
            .Line.Visible = True
        End With
       
' Use .Autosize to match size to contents, set protection and macro

        .AutoSize = True
        .Locked = True
        .OnAction = "'Select_Text_Box """ & PrdOrdNo & """'"
    End With
   
' Restore worksheet protection

    ActiveSheet.Protect UserInterfaceOnly:=True
   
' Reset environment

    Application.ScreenUpdating = True
End With


I get the error message at :

            .Characters(CharCount + 1).Insert String:=HeaderText(I)

I presume something has changed in Excel 2007/2010 and I now need to select the shape in a different way ?
0
Comment
Question by:lindsay_kirby
2 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39634227
Hi,

pls try to replace
        CharCount = 0
        For I = 1 To 3
        
' Header text is in bold font; body in regular
            .Characters(CharCount + 1).Insert String:=HeaderText(I)
            TextCount = Len(HeaderText(I))
            .Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = True
            CharCount = CharCount + TextCount
            .Characters(CharCount + 1).Insert String:=BodyText(I)
            TextCount = Len(BodyText(I))
            .Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = False
            CharCount = CharCount + TextCount
        Next

Open in new window

with this code

' Set the text sections

        CharCount = 0
        strShapeString = ""
        For i = 1 To 3
                strShapeString = strShapeString & HeaderText(i) & BodyText(i)
                .ShapeRange(1).TextFrame2.TextRange.Characters.Text = strShapeString
        Next
        For i = 1 To 3
            
' Header text is in bold font; body in regular

            ' Header text is in bold font; body in regular
            TextCount = Len(HeaderText(i))
            .ShapeRange(1).TextFrame2.TextRange.Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = True
            CharCount = CharCount + TextCount
            TextCount = Len(BodyText(i))
            .ShapeRange(1).TextFrame2.TextRange.Characters(Start:=CharCount + 1, Length:=TextCount).Font.Bold = False
            CharCount = CharCount + TextCount
        Next

Open in new window

The Method insert doesn't work anymore in XL2007 and further. The code creates firstly the text and then apply the formatting.

 Regards
0
 

Author Closing Comment

by:lindsay_kirby
ID: 39634370
Excellent response in terms of timing and detail !!  

Thankyou
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question