Excel 2003 VBA to Excel 2007 VBA help needed.

[JasonM]

Sponsored Links

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.Solid
    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
    Selection.ShapeRange.Line.style = 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

[JasonM]

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

[Fabian]

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.

[JasonM]

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"
    
    Windows(verstr).Activate
    ActiveSheet.Shapes("Picture 1").Select      ' Adds RFMD logo from HPAPLOT Macro file to the results file
    Selection.Copy
    Windows(Workbook).Activate
    ActiveSheet.Paste
    '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.

Jason