Pictures In Excel Worksheet [RESOLVED]

[recarv]

I've got code which inserts pictures into a worksheet. There may be more than one instance of the same file in the worksheet and I'd like to make a function which can delete a picture, but I don't know how to figure out which picture is the correct one to delete.

I looked around for information on the ActiveSheet.Pictures collection, but I can't find out if there is a unique identifier, how to reference it or how to set it upon adding the picture to the collection so I can use it as a reference later for deletion. Any information would be greatly appreciated!

Is there a way I can set a unique key for each inserted picture (whether its a unique picture filename to the worksheet or not) so I can delete the correct picture later using the key?

Thanks so much

Ross

[RobDog888]

Just use the pictures collection if they are not on a form.

MsgBox Application.Workbooks("Sheet1").Sheets.Item(1).Pictures.Count

[recarv]

Thanks, Thats what I was looking for, I couldn't find anywhere that provided a reference to the activesheet's pictures.

Thanks so much.

[recarv]

Ok,

I've got one more question - unrelated, but here goes:

I'd like to be able to have a user move and stretch/shrink a line in a spreadsheet. I give them a line to start with using:

ActiveSheet.Shapes.AddLine(x1,y1,x2,y2).Select

How can I get the new x1,y1,x2,y2 after they have moved and resized the line so I can store in a database and thus redraw these line(s) for them automatically later?

[RobDog888]

Ok, I have one more answer then.

Option Explicit
 
Private Sub Workbook_Open()
'Create the line on open of workbook.
    Dim oDocument As Worksheet
    
    Set oDocument = Worksheets(1)
    With oDocument.Shapes.AddLine(100, 100, 200, 300).Line
        .DashStyle = msoLineDashDotDot
        .ForeColor.RGB = RGB(50, 0, 128)
        .BeginArrowheadLength = msoArrowheadShort
        .BeginArrowheadStyle = msoArrowheadOval
        .BeginArrowheadWidth = msoArrowheadNarrow
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadStyle = msoArrowheadTriangle
        .EndArrowheadWidth = msoArrowheadWide
    End With
    oDocument.Shapes(1).Name = "Line1"
    'Assign it the macro of Line1_Click in this workbook - change to your wb name.
    oDocument.Shapes(1).OnAction = "Book1!ThisWorkBook.Line1_Click"
    Set oDocument = Nothing
    
End Sub
 
Public Sub Line1_Click()
 
    Dim oDocument As Worksheet
    Dim dblLeft As Double
    Dim dblTop As Double
    Dim dblWidth As Double
    Dim dblHeight As Double
    
    Set oDocument = Worksheets(1)
    With oDocument.Shapes("Line1")
        dblLeft = .Left
        dblTop = .Top
        dblWidth = .Width
        dblHeight = .Height
        MsgBox "Left: " & dblLeft & vbNewLine & "Top: " & dblTop & vbNewLine & _
        "Width: " & dblWidth & vbNewLine & "Height: " & dblHeight, vbOKOnly + vbInformation, _
        oDocument.Shapes("Line1").Name & " Coordinates"
    End With
    Set oDocument = Nothing
    
End Sub

VB/Outlook Guru!

[recarv]

Awesome,

Thanks so much for taking the time to help me.

R

[RobDog888]

Thanks. Not a problem. I actually like the way the code works.
Gives me some new ideas.

Ps, dont forget to resolve this thread. Look at my new signature!

[recarv]

Let me give some feedback to how I used the information.

With regard to the pictures collection, Once you told me how to find a reference to the picture I just inserted, I used the name property to assign it it's auto_incrmented key from the table. It works great now and avoids having to do annoying complete refreshes from the database (a simple redraw) because I can redraw selectively.

Second, with regard to the OnAction - using that code I arrived at the following which allows multiple lines to be inserted on a sheet and referenced accordingly:

ActiveSheet.Shapes(Application.ActiveWorkbook.Sheets.Item(1).Shapes.Count).name = "Line" & Application.ActiveWorkbook.Sheets.Item(1).Shapes.Count

Now, I guess since there seems to be at least a few people who may be interested in this (these) topics, I've got another question, this one with a little explanation because it a (i think) an pretty novel idea.

Preface: I'm attempting to write code which makes an excel spreadsheet act like an interactive blueprint designer( so I can make a map of a piece of land or a warehouse or whatever). I've been tasked to allow lines to be drawn to create polygons, then to create a scanline algorithm to determine which cells are within the polygon or not.

I've already got the warehouse part down because its all just rectangles, but doing polygons are much more difficult. I recall from college that in order to determine whether your inside or outside a polygon, you need a scanline algorithm.

I figure I need to make a scanline which compares cells to the line angle and distance.


Problem: Line in Excel seem to only have the attributes top,left and length width (this being because the 0,0 coordinate is always the top,left)

So, instead of giving up I'm trying to find a way to translate a starting coord (top,left) of a line into a cell address. Secondly, I'm trying to find a way to determine the slope of the line drawn (which is impossible(?) because there is never a negative value for length,width of the line.

Really this is more of an intellectual challenge because it involves making Excel do something it certainly wasn't intended to do.

[RobDog888]

For the slope of the line, don't know how to translate the top,
left, width, and height properties. Could they even be used to
determine to direction of the line?

[recarv]

I think because the width is always an unsigned integer we can't tell the direction which makes it impossible to find the slope.

Anyone know of another object which might accomplish the same thing as the line (for creating polygons) which might lend itself to a hacked up version of the scanline algorithm using the cells as the pixel unit?

Any API calls which could determine this?

[RobDog888]

Have you thought about using Visio? Actually, that is what your app
should be in, but I'm not sure what the project requirements are.

I thought i saw something in Excel on changing the coordinate
system from 0,0 being the top, left of the spreadsheet to 0,0
being the center, like a graph coordinates. Maybe you could
search on it in Excel?

HTH