Solved

drag and drop photo to excel cell and auto resize.

Posted on 2011-03-05
13
2,843 Views
Last Modified: 2012-05-11
I am working on an Excel 2010 spreadsheet and need to be able to drag and drop pictures into cells and the pictures automatically re size to the size of the cells. This spreadsheet is protected but the cells in questions will not be protected so the pictures will be able to be edited after they are inserted. I currently have a Macro setup with an insert photo button to pull up the insert picture box but due to the number of photos in this spreadsheet, i really need drag and drop capability to save time.

Thanks
0
Comment
Question by:DesktopSFS
  • 6
  • 5
  • 2
13 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 35044025
Are you aware that you cannot attach pictures to cells? Equally that you cannot insert a picture into a cell? You can move a picture to cover a cell or cells but it is not attached to any of those cells.

Patrick
0
 

Author Comment

by:DesktopSFS
ID: 35044090
I know images have to stay in the layer over the cells. At any rate do you have a suggestion or solution for my dilemma?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35044276
>At any rate do you have a suggestion or solution for my dilemma?

I'm afraid not.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044287
I think I may have a solution for you. Could you please confirm your Excel version? Is it 2003 or 2007 or 2010 or other?

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35044545
Since EE was down, I created a sample file for you. Please test it and let me know if this is what you want?

Imp Note: Before you run the userform, please change the path in the UserForm_Initialize() event

Attachements
1 sample file and 3 snapshots (Before, In Action and After)

Sid

Code Used

Private Sub UserForm_Initialize()
    '~~> I am loading the picture paths from this directory
    '~~> Please change the path here
    strpath = "G:\Users\Public\Pictures\Sample Pictures\"
    
    strfile = Dir(strpath & "*.jpg")
    
    Do While strfile <> ""
        ListBox1.AddItem strpath & strfile
        strfile = Dir
    Loop
End Sub

Private Sub CommandButton1_Click()
   Dim MyRange() As String
   On Error GoTo InsertPicError
   
   MyRange = Split(RefEdit1.Value, "!")
   
   InsertPic ListBox1.Text, Range(MyRange(UBound(MyRange)))
   
   Exit Sub
InsertPicError:
    MsgBox Err.Description
End Sub

Sub InsertPic(ImagePath As String, DestinationCell As Range)
    Dim pic As Object, tp As Double, lft As Double, wdt As Double, hgt As Double
    
    On Error GoTo Err
    Set pic = ActiveSheet.Pictures.Insert(ImagePath)
    
    '~~> Get Destination cell's dimentions
    With DestinationCell
        tp = .Top
        lft = .Left
        wdt = .Offset(0, .Columns.Count).Left - .Left
        hgt = .Offset(.Rows.Count, 0).Top - .Top
    End With
    
    '~~> Position picture as per Destination cell's dimentions
    With pic
        .Top = tp
        .Left = lft
        .Width = wdt
        .Height = hgt
    End With
    
    Set pic = Nothing
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window

Image-sample.xls
Snapshot1.jpg
Snapshot2.jpg
Snapshot3.jpg
0
 

Author Comment

by:DesktopSFS
ID: 35044567
Excel 2010. I've never used userforms before, pardon my ignorance but how do you get to the point where you select the file?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044589
>>>Excel 2010.

Let me create a button for you.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044593
Try this.

Sid
Image-sample.xlsm
0
 

Author Comment

by:DesktopSFS
ID: 35046436
That's really close to what I've got now, see attached, I just couldn't figure out how to make a button for a userform. The person I'm making this for really needs drag and drop because they are going to insert around 30 pictures in this sheet. I wonder if it would be easier to do this in Word since it has better art options?
EEcopysol.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35046702
You don't need so many buttons. Just one button will do and all you need to do is select the picture file and the cell and click on insert as shown in the file that I attached.

Sid
0
 

Author Comment

by:DesktopSFS
ID: 35047259
Is that the best I'll get, no way to make drag and drop work?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35047743
Had it been VB6/VB.NET then drag and drops are easier. The problem with Excel is that the worksheet doesn't have a drag event. So I cannot think of an option. Or maybe because I don't know of an alternative.

Sid
0
 

Author Closing Comment

by:DesktopSFS
ID: 35048188
Excel can't do what i need it to, but Sid gave me a work around that will suffice.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now