dslreports logo
    All Forums Hot Topics Gallery


how-to block ads

Search Topic:
share rss forum feed


Saint-Lazare, QC
·TekSavvy Cable

2 edits

[Excel] Add a frame into which user can click and add picture

Hi all

Using Excel 2010, I want to create an excel sheet that would allow users (very low computer knoledge) to simply click on a frame, and insert a picture. Basically, I'd like it to act exactly as powerpoint allows it.

It needs to restrict the dimension and location, as I don't want the picture to overlap text.
It would also be nice if it compressed it to a lower size.


EDIT: found this VB code that works 90%. It drops the picture where I want, but does NOT restrict the size it takes.

Sub test()
    Dim szPicFileName As String
    Dim rFirstRow As Long, rLastRow As Long
    Dim cFirstColumn As Integer, cLastColumn As Integer
     '   Get the filename & location of the picture
    szPicFileName = Application.GetOpenFilename()
    On Error Resume Next
    Set pic = ActiveSheet.Pictures.Insert(szPicFileName)
    On Error GoTo 0
    If Not pic Is Nothing Then 'Found it!'
         '   Set the range (change the first & last columns & rows to whatever you need)
        rFirstRow = 59
        rLastRow = 60
        cFirstColumn = 2
        cLastColumn = 12
        Set Rng = Range(Cells(rFirstRow, cFirstColumn), Cells(rLastRow, cLastColumn))
        With pic
            .Height = Rng.Height
            .Width = Rng.Width
            .Left = Rng.Left
            .Top = Rng.Top
        End With
    End If
End Sub


Saint-Lazare, QC

Re: [Excel] Add a frame into which user can click and add pictur

I'm guessing it's not possible...or I haven't made myself clear enough, please let me know.

How about coding the "change Picture" option that is enabled by right-clicking a picture. Is that codable through VB?


Saint-Lazare, QC
reply to nickstoy
please close. don't need it anymore, we went down an entirely other road

Premium,ExMod 1 BC
You sure you don't need it now? I know it's been some days, but I probably have code to do this. Matter of fact I did it just recently for a client.
Basically put the frame and size it where you want it to be first, then set up some code to do the work of browsing for and setting up the picture.
I'd put a simple rectangle shape on a sheet and gave it the name myPicture. The code below runs when you click on it (by assigning this macro to the shape).
Since VBA really doesn't know how it got to that routine, I search the active sheet for all shapes and when I find the correct one, I set up the picture in it.
Sub ChooseNewPicture()
  Dim someShape As Shape
  Dim newLogo As String
  Dim myReply As Integer
  myReply = MsgBox("Choose New Picture [Yes] or Remove Picture [No]?", _
   vbYesNoCancel + vbQuestion, "Picture Choices")
  Select Case myReply
    Case Is = vbNo
      'remove fill from the logo
      For Each someShape In Sheet1.Shapes
        If someShape.Name = "myPicture" Then
          With someShape.Fill
            .Visible = False
          End With
        End If
    Case Is = vbYes
      newLogo = Application.GetOpenFilename("Graphic Files (*.jpg; *.gif),*.jpg;*.gif", , _
       "Select Logo File")
      If newLogo = "False" Then
        Exit Sub
      End If
      For Each someShape In Sheet1.Shapes
        If someShape.Name = "myPicture" Then
          With someShape.Fill
            .Visible = True
            .UserPicture newLogo
          End With
        End If
    Case Else
      'vbCancel or treat same as vbCancel
      Exit Sub
    End Select
End Sub

...then THINK! again.