Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Excel 2003 VBA to Excel 2007 VBA help needed.

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 29-01-2009
Member
 
Join Date: Jan 2009
Posts: 3
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

Reply With Quote
  #2  
Old 29-01-2009
Member
 
Join Date: Jan 2009
Posts: 3
Re: Excel 2003 VBA to Excel 2007 VBA help needed.

Forgot to say thank-you in advace for any help!
Reply With Quote
  #3  
Old 29-01-2009
Member
 
Join Date: May 2008
Posts: 1,805
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.
__________________
Darksiders: Wrath of War
Reply With Quote
  #4  
Old 29-01-2009
Member
 
Join Date: Jan 2009
Posts: 3
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Excel 2003 VBA to Excel 2007 VBA help needed."
Thread Thread Starter Forum Replies Last Post
Excel 2003 Macro doesn't work in Excel 2007 jjaw Windows Software 3 03-01-2014 02:28 PM
Help needed to add a Chart with a macro in Excel 2007 Clemens Windows Software 3 18-11-2010 11:31 PM
How to Run Excel 2003 Macros in Excel 2007 Ekanga Windows Software 5 19-07-2010 02:37 PM
Excel 2007 macro help needed AZUL Software Development 4 19-11-2009 05:10 PM
Exporting from Project 2007 to Word 2003 or Excel 2003 Jasper W Microsoft Project 5 20-04-2009 11:52 PM


All times are GMT +5.5. The time now is 07:50 PM.