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.
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:=""
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...
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.
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.
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.
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.
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:
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
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")
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
