User Form for view, inserting or changing a photo in a cell

[bisel]

Hello All,

I am a novice at writing efficient VBA code and hoping someone might be able to help me.

I have a workbook and would like to use the Excel capability to insert an image into a cell by having the user select an image located on their computer's hard drive somewhere. I like the idea of inserting an image into a cell because of the capability to retain sort order and filtering.

My concept is that the user would click to select a cell on the sheet and then give them the option to edit the image associated with that cell. If the user selects that option to edit the image, then have a user form open. Probably call the user form "Image Editor" or something like that. When the form initializes, I want the form to look like something like this ...

The User Form will initialize and show a preview of the current image associated with the cell that the user selects. The image will be in the cell not overlayed over the cell. Of course, if there is no image in the cell, merely show a blank. I then would think of having two control buttons to remove or replace the current image. My thought is that if there is no image in the cell, then the replace button would merely add one from a folder that the user would select. Lastly, the close the button would merely close the user form.

I know how to create simple user forms and understand the concept of initialize and activate events. Closing the form is no problem for me. What I need assistance with is how to create the VBA code so that when the user selects the component on the sheet, the form would initialize and show the preview of the currently associated image (if there is one).

Has anyone done anything like this? Can you point me in the right direction?

Thanks,

Steve

 

[Micron]

No, and not really. But I can offer a suggestion or two about how things could also work. I'm not seeing the point of a userform like that given that it can't edit the image. Nor can it display an image for initial examination unless you assign a picture property to an image control, and to do that you'd have to select an image from a folder first. Windows already has the capability for displaying picture files as thumbnails anyway, so that would make duplicating the feature kind of pointless.

What I might do if there is no picture in the clicked cell is:
- use sheet selectionchange or one of the click events to open a dialog box (msoFileDialogFolderPicker). User navigates to a folder and can only see image files in them.
- if user chooses an image, place it in the cell that was selected. If not, cancel out.

If there is already a picture in the cell then that's where it gets tricky. Images can have click events but I have no idea if you can create and assign one during the process that places the image on the sheet. I doubt it but am not certain. What I believe can be done is a custom class that uses WithEvents. I know next to nothing about that except that it is a way of getting code to run whenever a recognized event is triggered but the object being clicked on doesn't have a macro assigned to it.

So in the absence of a better answer here, maybe take a look at the Office dialog I mentioned as well as WithEvents.

 

[bisel]

No, and not really. But I can offer a suggestion or two about how things could also work. I'm not seeing the point of a userform like that given that it can't edit the image. Nor can it display an image for initial examination unless you assign a picture property to an image control, and to do that you'd have to select an image from a folder first. Windows already has the capability for displaying picture files as thumbnails anyway, so that would make duplicating the feature kind of pointless.

What I might do if there is no picture in the clicked cell is:
- use sheet selectionchange or one of the click events to open a dialog box (msoFileDialogFolderPicker). User navigates to a folder and can only see image files in them.
- if user chooses an image, place it in the cell that was selected. If not, cancel out.

If there is already a picture in the cell then that's where it gets tricky. Images can have click events but I have no idea if you can create and assign one during the process that places the image on the sheet. I doubt it but am not certain. What I believe can be done is a custom class that uses WithEvents. I know next to nothing about that except that it is a way of getting code to run whenever a recognized event is triggered but the object being clicked on doesn't have a macro assigned to it.

So in the absence of a better answer here, maybe take a look at the Office dialog I mentioned as well as WithEvents.

Thank you for taking the time to think about this and post a reply. Let me give some background ...

The worksheet is an inventory sheet for physical components that an inspector might be performing an evaluation for general condition. I have set aside a cell within a table that can be used to insert an image of the object. I want to use "insert image in cell" rather than "insert image on top of cells" because I need to retain the ability of performing filtering and sorts on the table. The table is, as you know, a matrix with normal height rows and columns. When one inserts an image in the cell, it takes on the cell dimensions and would be quite small. But the image still retains the original properties (width, length and resolution) so that if enlarged one more than likely can then view the image.

I want the user form for several reasons ...

• I don't want the user to mistakenly insert the image "over the cells". I need to ensure that image inserted into the table is inserted in the cell.
• Not all components will necessarily have an image associated with the various components. This is entirely optional.
• I need to retain the ability to perform filtering and sorting on other criteria in the table. By inserting images in the cell, this will be allowed.
• The the column where the image cells will be living will normally be hidden from view. There will be an indicator cell to show that an image is present. Perhaps using the selection event on the indicator cell may be a good way to call the user form to view, delete, replace the image. Again ... the reason for the user form is to ensure that the user inserts any image into the target cell using the insert, picture, place in cell option and not the insert, picture, place over cells option.

Thanks for your insight.

Regards,

Steve

 

[Micron]

That clears up a few things for me - to the point where I won't be able to help. What I can say with a high degree of certainty is that this is a job for Access, but I doubt that comment will move you in that direction. Access has a long learning curve if it is to be done right, but it's definitely the tool for this job.
Hope you receive a solution that works for you.

 

[bisel]

That clears up a few things for me - to the point where I won't be able to help. What I can say with a high degree of certainty is that this is a job for Access, but I doubt that comment will move you in that direction. Access has a long learning curve if it is to be done right, but it's definitely the tool for this job.
Hope you receive a solution that works for you.

I agree that Access would have been a better solution. I have looked at Access and the relational database structure would adapt nicely to what I am attempting, however ... as you say ... the learning curve for Access is long. My skills in that arena are very limited. I am several years into this application using Excel as the underpinning and so far it is meeting my needs. That said, there are limitation in Excel and sometimes the workarounds are a bit of a kluge.

 

[Micron]

Maybe a bit of a stretch, but when using Get Data (ribbon) you can pull data in from Access and if I recall correctly, that can be a table or a query. It may provide you with the ability to make relationships between db tables and then group records via queries that you can pull from. However, if this involves images then I don't think that will work because a query cannot return an image. It can return an image path though. Also, if this is to be like a BOM (bill of materials) type of db then it's one of the most difficult to design because of its recursive nature.

When one inserts an image in the cell, it takes on the cell dimensions and would be quite small. But the image still retains the original properties (width, length and resolution) so that if enlarged one more than likely can then view the image.

This I had no idea about because I'm not as adept with Excel as I am with Access, so that's why I said I wouldn't be able to help. At least that would be my first impression.

 

[NdNoviceHlp]

Hi bisel. It's another snow day on the prairies so I've come up with some code that hopefully will be useful for you. It seems that "insert image in cell" rather than "insert image on top of cells" is no longer possible with XL. Both insert picture and add a shape picture to the cell seem to result in the picture on top of the cell. Beyond that, it seems impossible to detect a cell selection event from a cell that has a picture superimposed on top of it. But don't despair, i think you will like the following approach better as long as it is compatible with your need to retain the ability of performing filtering and sorts on the table (which I don't know). Anyways, you can insert pictures in cell comments so that when you hover over the comment the full pic is displayed. You can also select the cell that contains the comment which allows you to edit the cell comment pic. Picture editing occurs as you outlined via the use of a userform. So to get set up, insert a userform (Userform1). Add a large image control (Image1); x3 command buttons (C1 is Load pics from folder, C2 is insert/replace picture, C3 is delete picture); and 1 listbox (listbox1). If no comments in the sheet exist, the userform will display when any cell is selected. Following the initial picture/comment insertion, you need to select a cell with a comment to show the userform. The userform is modeless so after it is displayed, you can add/replace pics with the userform displayed. So to edit an existing pic/comment, select the cell where the pic is displayed. The userform image displays the selection pic. Select the C1 button and load your folder image to the listbox. Listbox selections replace the image shown on the userform. Select C2 to replace the selection picture with the image displayed on the userform. Select C3 if you just want to delete the picture from the selection. To add new Pictures/comments to a selected cell (when the userform is displayed) select your listbox image to display the image on the userform, then select C2 to place the displayed picture in the selected cell. It's easier than it types. HTH. Dave
Sheet code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'show userform to start if no comments
If ActiveSheet.Comments.Count = 0 Then
Set Rng = Target
UserForm1.Show vbModeless
Exit Sub
End If

With Target
'if cell with comment selectd
If Not .Comment Is Nothing Then
    If MsgBox(prompt:="Do you want to change this picture?", Buttons:=vbYesNo, _
                         Title:="EDIT PICTURE?") = vbYes Then
    PicPath = .Comment.Text
        'check if userform is loaded
        If IsLoaded("UserForm1") Then
        With UserForm1.Image1
          'if loaded (vbModeless) update image control with comment pic
          If .Picture Is Nothing Then
             .Picture = LoadPicture(PicPath)
          Else
          .Picture = LoadPicture(vbNullString)
          .Picture = LoadPicture(PicPath)
          End If
        End With
        Else
        UserForm1.Show vbModeless
        End If
    End If
End If
End With
'set "Rng" to selected cell
Set Rng = Target
End Sub

Userform code....

Option Explicit
Dim ArrPath() As Variant

Private Sub UserForm_Initialize()
UserForm1.Image1.PictureSizeMode = fmPictureSizeModeStretch
UserForm1.Image1.Picture = LoadPicture(PicPath)
End Sub

Private Sub CommandButton1_Click()
'LOAD FILES
'load listbox pic files from folder
Dim SFolder As Object, sFileName As Object, FSO As Object, Cnt As Integer, ArrName() As Variant

Set FSO = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then ' if OK is pressed
Set SFolder = FSO.GetFolder(.SelectedItems(1))
Else
MsgBox "No folder selected"
Set FSO = Nothing
Exit Sub
End If
End With

'Loop through each file in a folder.
'Load pic files path and name to arrays
For Each sFileName In SFolder.Files
If InStr(sFileName.Type, "GIF") Or InStr(sFileName.Type, "JPEG") Then
Cnt = Cnt + 1
ReDim Preserve ArrPath(Cnt)
ReDim Preserve ArrName(Cnt)
ArrPath(Cnt - 1) = sFileName.Path
ArrName(Cnt - 1) = sFileName.Name
End If
Next sFileName
UserForm1.ListBox1.List = ArrName
Set FSO = Nothing
End Sub

Private Sub CommandButton2_Click()
'INSERT/REPLACE Pictures
'load sheet selection cell comment with displayed pic
If UserForm1.ListBox1.ListIndex = -1 Then
MsgBox "Select image"
Exit Sub
End If
With Rng
'delete previous comment
If Not .Comment Is Nothing Then
.Comment.Delete
End If
.AddComment
.Comment.Text Text:=ArrPath(UserForm1.ListBox1.ListIndex)
.Comment.Shape.Fill.UserPicture ArrPath(UserForm1.ListBox1.ListIndex)
'****adjust to suit
.Comment.Shape.ScaleHeight 6, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleWidth 4.8, msoFalse, msoScaleFromTopLeft
End With
End Sub

Private Sub CommandButton3_Click()
'DELETE PICTURE
With Rng
'delete comment
If Not .Comment Is Nothing Then
.Comment.Delete
Else
MsgBox "No picture in selection to delete."
End If
End With
End Sub

Private Sub ListBox1_Click()
'load image1 with pic
With UserForm1.Image1
  If .Picture Is Nothing Then
     .Picture = LoadPicture(ArrPath(UserForm1.ListBox1.ListIndex))
  Else
  .Picture = LoadPicture(vbNullString)
  .Picture = LoadPicture(ArrPath(UserForm1.ListBox1.ListIndex))
  End If
End With
End Sub

Module code...

Public PicPath As String, Rng As Range
Public Function IsLoaded(formName As String) As Boolean
'check if userform is loaded
Dim frm As Object
For Each frm In VBA.UserForms
    If LCase(frm.Name) = LCase(formName) Then
        IsLoaded = True
        Exit Function
    End If
Next frm
IsLoaded = False
End Function