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:
Code:
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
Re: Excel 2003 VBA to Excel 2007 VBA help needed.
Forgot to say thank-you in advace for any help!
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.
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:
Code:
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:
Code:
Run-time error '9':
Subscript out of range
I thought this might be due to the line before and changed it to
Code:
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