Insert a photo in front of command button

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Insert a photo in front of command button

Post by ABabeNChrist »

I have a commend button that is located within a cell area, I use this button to insert a picture using the code below, everything works great except the command button is visible in front of the picture. I set the properties for the command button so it will not print, but of course that is for printing only. Is it possible to have command button go behind the picture once inserted, I dont want the button deleted, just in case wrong picture were to get inserted a correction can be made.

Code: Select all

  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range
    
    Set MyRange = Range("AD64:AK64")
    ActiveSheet.Unprotect Password:=""
    
    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation = "False" Then Exit Sub
    
    Set Pic = MyRange.Parent.Pictures.Insert(PicLocation)
    
      With Pic.ShapeRange
        .Left = MyRange.Left
        .Top = MyRange.Top
        .LockAspectRatio = msoFalse
        .ZOrder msoBringForward
        If .Width > .Height Then
           .Width = MyRange.Width
           If .Height > MyRange.Height Then .Height = MyRange.Height
        Else
            .Height = MyRange.Height
            If .Width > MyRange.Width Then .Width = MyRange.Width
        End If
      End With
    
    With Pic
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("AD65").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""

User avatar
Leif
Administrator
Posts: 7292
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Insert a photo in front of command button

Post by Leif »

ABabeNChrist wrote: I dont want the button deleted, just in case wrong picture were to get inserted a correction can be made.
If the button goes behind the picture, how are you going to access it if you need to change it?
Leif

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Insert a photo in front of command button

Post by sdckapr »

I don't understand why you need the button at all. You can just have the code in a macro in a module and run it whenever you want by using tools- macro or going to VB...

Steve

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

Hi Leif and Steve
Let me try and explain a little more of my method of madness. I have a couple of different spreadsheets within the same workbook that are used to do report with, each report may have between 10 and 20 different areas/sections to report on. Now let’s say I see no concerns pertaining to section 1 of the report, I note it and move on to section 2 of the report. Now on section 2 I find some concerns that not only need to be noted but identified by using a photo. Just below my noted comment I have a small check box that when checked will unhide a row below. The hidden row below has been adjusted height and each cell width to allow a picture to be inserted, into 4 locations within that hidden row. I then put a command button in each of the 4 cells. Of course none of these buttons are visible while row is hidden. Each command button refers to that cell; also each command button will not print that’s in case I only insert 1 or 2 pictures. I repeated this hidden row process on the bottom of each section. This way my report will stay more condensed with not a bunch of open space. Below I have attached a sample copy of how a report may appear. I removed any sensitive material. I’m just a beginner at this, trying to learn new things, so any helpful advice is always greatly appreciated.
Book1.xlsb
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79522
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert a photo in front of command button

Post by HansV »

Why don't you use a single toolbar (or QAT) button instead of laboriously inserting command buttons in lots of cells?

Alternatively, you could use the Worksheet_BeforeRightClick or Worksheet_BeforeDoubleClick events.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

Hi HansV
Your question
Why don't you use a single toolbar (or QAT) button instead of laboriously inserting command buttons in lots of cells?
I quite agree with you 100% but at the time when I was setting up my sheets I was unable to find any code that would allow me to insert and size a photo of a selected cell, except for the one I post at the beginning of my thread were it refers to a selected range. I’m trying to keep this workbook from getting too much on it that it takes all day just to open. And eliminating a mess load of button is a good start.

I'm not sure what you mean by this
Alternatively, you could use the Worksheet_BeforeRightClick or Worksheet_BeforeDoubleClick events.

User avatar
HansV
Administrator
Posts: 79522
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert a photo in front of command button

Post by HansV »

In the code behind a toolbar button, you can still refer to the currently selected cell.

By "Alternatively, you could use the Worksheet_BeforeRightClick or Worksheet_BeforeDoubleClick events" I meant that you could instruct users to right-click or double-click a cell if they want to insert a picture.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

Hi HansV
I tried both ways and I don’t think I will be able to use either method (of course I'm not really sure)
When I tried to use

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Using this method the double click event comes into play with every cell I double click into, and I need the double click to enter data to a Data Validation cell so that I can populate my dynamic name range if needed. And of course I use the right click event to execute task, I’m sure I’m missing something here. :confused:

User avatar
HansV
Administrator
Posts: 79522
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert a photo in front of command button

Post by HansV »

You can inspect the Target - it's a Range object that tells you which cell has been double-clicked. Let's say you want to perform a non-standard action if the user double-clicks B10, D10 or F10:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Range("B10,D10,F10"), Target) Is Nothing Then
    Cancel = True ' Don't perform the standard action
    ' Your code here; Target is the cell being double-clicked
    ...
  End If
End Sub
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

Hi Hans
I tried as you mentioned and I recieved a Run Error 91, Object Variable or Blocked Variable Not Set
and this part of code was highlighted

Code: Select all

Set Pic = MyRange.Parent.Pictures.Insert(PicLocation)

User avatar
HansV
Administrator
Posts: 79522
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert a photo in front of command button

Post by HansV »

Try

Set Pic = Me.Pictures.Insert(PicLocation)
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

I am still getting the same run time error
now it has moved to

Code: Select all

.Left = MyRange.Left

User avatar
HansV
Administrator
Posts: 79522
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert a photo in front of command button

Post by HansV »

Try replacing all references to MyRange with Target.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Insert a photo in front of command button

Post by ABabeNChrist »

Thank you HansV
That seemed to solve the the problem
I knew that "MyRange" was the problem I just didnt have a clue what to replace it with.
I also tested it with a merged cell only using the referance to cell, not range ('B42:B45")

Here is the finish results

Code: Select all

  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Range("B42"), Target) Is Nothing Then
    Cancel = True ' Don't perform the standard action
    ' Your code here; Target is the cell being double-clicked

  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range
    
    ActiveSheet.Unprotect Password:=""
    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation = "False" Then Exit Sub
    
    Set Pic = Me.Pictures.Insert(PicLocation)
    
      With Pic.ShapeRange
        .Left = Target.Left
        .Top = Target.Top
        .LockAspectRatio = msoFalse
        .ZOrder msoBringForward
        If .Width > .Height Then
           .Width = Target.Width
           If .Height > Target.Height Then .Height = Target.Height
        Else
            .Height = Target.Height
            If .Width > Target.Width Then .Width = Target.Width
        End If
      End With
    
    With Pic
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""
End If
End Sub