Works in 2003 but not in 2007

[dcoledc]

I recorded this macro in Excel 2003 and it works fine:

Sub Vertical()
    ActiveSheet.Unprotect
    ActiveSheet.Shapes.AddLine(597.75, 57, 597.75, 600#).Select
    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.SchemeColor = 10
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Line.BeginArrowheadLength = msoArrowheadLengthMedium
    Selection.ShapeRange.Line.BeginArrowheadWidth = msoArrowheadWidthMedium
    Selection.ShapeRange.Line.BeginArrowheadStyle = msoArrowheadNone
    Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
    Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadNone
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 580
    Selection.ShapeRange.Width = 0#
    Selection.ShapeRange.Rotation = 0#
    Selection.Locked = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
End Sub

Two question.
1. Does anyone have any idea why this code would not work in Excel 2007? I don't have excel 2007 so I can't try recording it on there and comparing. I found out it doesn't work from a user.

2. Is there a better way to write this code? Basically, I want to hit the macro and a line appears in a specific location on the sheet for the user.

Thanks, Dan

 

[dcoledc]

P.S. running the macro creates a run-time error - specified value out of range.

 

[dave3009]

Take out this line

Selection.ShapeRange.Fill.Transparency = 0#

Seems you can not long fill a shape without a fill area

I'll get back to you on the rest

EDIT:dcoledc - It was an invalid call debugged with F8 through the code

 

[dcoledc]

That was quick and I will give it a try.

Thanks, Dan

 

[dave3009]

Try this

Sub Vertical()
   With ActiveSheet
    .Unprotect
        ActiveSheet.Shapes.AddLine(597.75, 57, 597.75, 600#).Select
        With Selection
            .ShapeRange.Line.Weight = 0.75
            .ShapeRange.Line.ForeColor.SchemeColor = 10
            .ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
            .ShapeRange.Height = 580
            .Locked = False
        End With
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   End With
End Sub

 

[dcoledc]

Both suggestions worked great. Thank you.

If you don't mind, I have another problem, again when going from 2003 to 2007. I recorded a macro that created a textbox with wording in it that would be placed on the sheet. At the time worked great. I changed it, however, to where it calls a .png file I created in photoshop. I got ride of the textbox and the code that created it.

Calling the .png works great in my 2003 version and 2007, but when opens the file, not runs the macro, but just opens the file, they see the old textbox, without hitting a macro or anything. Any suggestions on how to fix this?

Thanks again, Dan

 

[dave3009]

Sounds like this might have deeper roots than just the calling of the png file. What is it you're trying to do with the png, call it to a sheet, call it to an image frame or just open it?

 

[dcoledc]

I should give you some background information on me and my VBA knowledge. I am new to VBA and started by recording macros. As I got into it, I found that this is somewhat limited. I have been able to do what I want, mostly by looking on the internet and then adapting the code to my needs. This forum has helped me alot as well.

Having said that, I may not be using all the correct terminology. I will give you the specifics of what I am trying to do. Before recording my macro, I did the following:

1. Created a textbox with a word in it.
2. Created an arrow to be placed next to the word
3. Created a rectangular shape
4. I placed the word and the arrow within the rectangle and then grouped them all together.
5. Moved this grouped object somewhere around row 100, so the user wouldn't see it.

Once this was done, I started recording these steps:

1. Copied the grouped object
2. Pasted it where I wanted it to end up
2. Stop recording

THis worked fine but I thought it was a little tacky. So I got rid of it all. I will say that when I tried to ungroup this object it caused the program to crash. Eventually, I didn't ungroup and I just deleted it.

Anyway, I thought a better solution would be to create a .png in photoshop. The .png is the word with an arrow next to it, just like what I had created in excel.

The code I am now using follows:

Sub LeftSub()
    ActiveSheet.Unprotect
    Range("P7").Select
    ActiveSheet.Pictures.Insert( _
        "C:\Program Files\Reports to Go\reports to go\Left Subluxation.png").Select
    Selection.Locked = False
    Selection.ShapeRange.Top = ActiveCell.Top
    Selection.ShapeRange.Left = ActiveCell.Left
    ActiveSheet.Protect
    
End Sub

Again, this works great in both 2003 and 2007. The problem is when a user opens the file in 2007 they see the old words and arrows just sitting there. I know it is the old stuff, b/c the user can click on it and delete single letters which tells me it is not the .png showing up but the old typed out word.

Hope that wasn't too much, but any help would be great.

 

[dave3009]

Is the png just some kind of unique button or call to action type thing, if so you might consider imbedding it and just hiding it until it is needed.

 

[dcoledc]

I am not sure I understand your question, but .png is the extension and the file is an image. When I hit my macro button within my worksheet, it runs the code from the previous post and inserts the image or .png file onto my sheet.

Thanks, Dan