none
Run Time error 438 RRS feed

  • Question

  • Hi,

    Building a report in 2010, but we do have a large part of users on 2003, Excel opens within the browser, I am having an issue which I never come across. A piece of code  that I have when a sheet is activated works in 2010 but I get a "Run-Time error 438" in 2003, any help is appreciated.

    Private Sub Worksheet_Activate()
        Call Shape
          
            If Range("J76").Value = "Not Found" Then
            ActiveWorkbook.Close False
            Exit Sub
        End If
    ' Change according to the needs
        ActiveWindow.Zoom = 85
        ActiveWindow.Zoom = 80
    End Sub

    The code for "Shape" is within a module in the same project:

    Function Shape()
    ' Update Shape1 with value from cell J76
        
        ActiveSheet.Shapes.Range(Array("Shape2")).Select
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Range("J76").Value
        Range("A1").Select
            
            If Range("H76").Value = "Admin" Then
            Exit Function
            ElseIf Range("J76").Value = "Not Found" Then
            MsgBox "You currently do not have access to this file, if you believe you should," _
            & vbCrLf & _
            "please contact the Analysis & Incentive Team.", _
            vbOKOnly, _
            "PERMISSIONS"
        Exit Function
            End If
    End Function

    Thanks in advance,

    Monday, March 4, 2013 12:21 PM

Answers

All replies

  • Textframe2 did not exist in 2003.  Learn more at

    http://peltiertech.com/WordPress/programming-excel-2007-2010-autoshapes-with-vba/

    "The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code."

    • Marked as answer by vdafonse Monday, March 4, 2013 2:12 PM
    Monday, March 4, 2013 1:41 PM
  • Textframe2 did not exist in 2003.  Learn more at

    http://peltiertech.com/WordPress/programming-excel-2007-2010-autoshapes-with-vba/

    "The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code."

    Hi Bernie,

    Think I got it, (ignore other message been deleted), persistence is the name of the game :-)

       

    I've just replaced: 

        'Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Range("J76").Value

    by

        Selection.Characters.Text = Range("J76").Value

    and seems to work

    Thanks for the help,


    • Edited by vdafonse Monday, March 4, 2013 2:13 PM add comment
    Monday, March 4, 2013 2:11 PM
  • But you never need to select. Replace

    ActiveSheet.Shapes.Range(Array("Shape2")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Range("J76").Value
    Range("A1").Select

    with

    ActiveSheet.Shapes("Shape2").TextFrame.Characters.Text = Range("J76").Value

    Monday, March 4, 2013 2:22 PM
  • But you never need to select. Replace

    ActiveSheet.Shapes.Range(Array("Shape2")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Range("J76").Value
    Range("A1").Select

    with

    ActiveSheet.Shapes("Shape2").TextFrame.Characters.Text = Range("J76").Value

    Many Thanks again Bernie,

    May I ask you another quick question, sorry, the value in the cell is formatted as "£#, ##0. 00" however within the shape appears as with the "$" sign any idea why and will be possible to change he format?

    I've tried "With Selection.CellFormat" and other variations but I don't seem to get it working.

    Thanks in advance,

    Monday, March 4, 2013 3:12 PM
  • ActiveSheet.Shapes("Shape2").TextFrame.Characters.Text = Format(Range("J76").Value,"£#, ##0. 00")

    • Marked as answer by vdafonse Monday, March 4, 2013 4:04 PM
    Monday, March 4, 2013 3:43 PM
  • ActiveSheet.Shapes("Shape2").TextFrame.Characters.Text = Format(Range("J76").Value,"£#, ##0. 00")

    Thanks Bernie,

    I think sometimes I try to complicate to much :-)

    All the best,

    Monday, March 4, 2013 4:04 PM