Results 1 to 4 of 4

Thread: Excel 2003 VBA to Excel 2007 VBA help needed.

  1. #1
    Join Date
    Jan 2009

    Excel 2003 VBA to Excel 2007 VBA help needed.

    Hello all. First off, I am a novice to VBA programming, but I do have a small background in c++, perl, and some other odd scripting languages.

    I am running:
    Windows XP sp2
    Office 2007

    Here is the problem that I am faced with. I have a macro that is adding a small rectangular textbox with some variables that are entered at the beginning of the macro ontop of 4 charts. It works in 2003, but when I run the macro in 2007 it doesn't add the small textbox at all. Any suggestions:

    Public Sub chartstitle()
    ' Insert text box between 4 graphs with type of data, part number and serial number
    Dim xplace, yplace As String
    Dim charttitlesize As Single
    charttitlesize = Fontsize + 4
        ActiveWindow.Visible = True     ' False
        ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 470#, 300#, 0#, 0#).Select    ' Positions text box in center of four quad charts
        Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue
        Selection.Characters.Text = datasheet & " Data" & Chr(10) & PartRef & Chr(10) & "s/n: " & SerialRef ' Creates character string with returns of three text attributes
        Selection.HorizontalAlignment = xlCenter        ' Sets text to be center aligned
        With Selection.Font                         ' Sets text box font attributes
            .Name = Fontstr
            .FontStyle = "Bold"
            .Size = charttitlesize
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
        Selection.ShapeRange.Fill.Visible = msoTrue             ' Sets box attributes including color, light yellow
        Selection.ShapeRange.Fill.ForeColor.RGB = RGB(201, 195, 127)
        Selection.ShapeRange.Fill.Transparency = 0#
        Selection.ShapeRange.Line.Weight = 0.75
        Selection.ShapeRange.Line.DashStyle = msoLineSolid = msoLineSingle
        Selection.ShapeRange.Line.Transparency = 0#
        Selection.ShapeRange.Line.Visible = msoTrue
        Selection.ShapeRange.Line.ForeColor.RGB = RGB(201, 195, 127)
        Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
        ActiveSheet.Range("A1:V51").Select      'Ends selecting all graph range ready for user to copy and paste without selecting again
    End Sub

  2. #2
    Join Date
    Jan 2009

    Re: Excel 2003 VBA to Excel 2007 VBA help needed.

    Forgot to say thank-you in advace for any help!

  3. #3
    Join Date
    May 2008

    Re: Excel 2003 VBA to Excel 2007 VBA help needed.

    The automation functionality provided by VBA has caused Excel to become a target for macro viruses. To prevent this, Microsoft disabled the macros completely. To enable macros, open a workbook and save it as .xlsm, .xlsb, .xltm, or .xlam.

  4. #4
    Join Date
    Jan 2009

    Re: Excel 2003 VBA to Excel 2007 VBA help needed.

    I forgot to state that I had macro's enabled.

    As I stated in the original post, the macro runs, but does not put the small textbox in the middle of the 4 charts. In fact, it doesn't even add a textbox anywhere on any of the sheets.

    I did as you suggested though to see if that would fix the problem, but now I am running in to other errors before the macro even gets to the code posted above.

    The new problem is located here:

    Public Sub Graph2Position()
    Dim verstr
    ' Position Graph2 in upper right
    L = L + W
        ActiveChart.Location Where:=xlLocationAsObject, Name:=chartsheet
        With ActiveChart
            .Parent.Width = W
            .Parent.Height = H
            .Parent.Top = T
            .Parent.Left = L
        End With
        verstr = version & ".xls"
        ActiveSheet.Shapes("Picture 1").Select      ' Adds RFMD logo from HPAPLOT Macro file to the results file
        'Selection.ShapeRange.IncrementLeft 927.75
    End Sub
    And stops with the error message:
    Run-time error '9':
    Subscript out of range
    I thought this might be due to the line before and changed it to

    verstr = version & ".xlsx"
    But that did not work either. I think that it is calling for another xls file, but I have no idea which one right now. I will try and figure that out.

    As I stated earlier, I am a complete novice at VB and have long since realized that I am in over my head.


Similar Threads

  1. Excel 2003 Macro doesn't work in Excel 2007
    By jjaw in forum Windows Software
    Replies: 3
    Last Post: 03-01-2014, 03:28 PM
  2. Help needed to add a Chart with a macro in Excel 2007
    By Clemens in forum Windows Software
    Replies: 3
    Last Post: 19-11-2010, 12:31 AM
  3. How to Run Excel 2003 Macros in Excel 2007
    By Ekanga in forum Windows Software
    Replies: 5
    Last Post: 19-07-2010, 03:37 PM
  4. Excel 2007 macro help needed
    By AZUL in forum Software Development
    Replies: 4
    Last Post: 19-11-2009, 06:10 PM
  5. Exporting from Project 2007 to Word 2003 or Excel 2003
    By Jasper W in forum Microsoft Project
    Replies: 5
    Last Post: 21-04-2009, 12:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts