inserting two picture same size over 4 cells square

[carrguy01]

i have the below program working! but ran into some cell issue when i make the picture i want it does not let me do smaller cells to the right of the picture. Can i for example use the same kind of formula say from b2 to e2 wide and b2 to f5 high. So i can use the the cells next to the picture for math formuls?

Sub piccy()
ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\85938.jpg")
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("c3").Top
.Left = Range("c3").Left
.Height = Range("c3").RowHeight
.Width = Range("c3").Width
End With
ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\85938.jpg")
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("f3").Top
.Left = Range("f3").Left
.Height = Range("f3").RowHeight
.Width = Range("f3").Width
End With
End Sub

 

[jeffreybrown]

Try this...

Adjust the dimensions as required

Sub piccy()
    Dim x As Long: x = 2
    Dim y As Long: y = 5
    ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\85938.jpg")
    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
        .LockAspectRatio = False
        .Top = Range("B2").Top
        .Left = Range("B2").Left
        .Height = Range("B" & x & ":" & "B" & y).Height
        .Width = Range("B" & x & ":" & "E" & x).Width
    End With
    ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\85938.jpg")
    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
        .LockAspectRatio = False
        .Top = Range("F2").Top
        .Left = Range("F2").Left
        .Height = Range("F" & x & ":" & "F" & y).Height
        .Width = Range("F" & x & ":" & "H" & x).Width
    End With
End Sub

 

[carrguy01]

what in the fourmula do chnage for height. I figured out the width

 

[carrguy01]

i can move the cells aroud the page i get that part of the forumal. I can adjust the width of the picture. But i am struggling with the hieght what in the formula above do i adjust to modify the hieght?

<TABLE style="WIDTH: 217pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=289><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=68></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=65></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=28></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 24.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=33 vAlign=top align=left><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 24.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=33 width=68></TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

 

[jeffreybrown]

The code has two variables, x and y

Dim x As Long: x = 2
Dim y As Long: y = 5

For the first picture, the Top and Left are set to Cell B2.

.Top = Range("B2").Top
.Left = Range("B2").Left

The Height and Width use the variables (so you can adjust in one place rather than throughout the code), but if you don't want any variables then you can hardcode your dimensions.

With varibles

.Height = Range("B" & x & ":" & "B" & y).Height
.Width = Range("B" & x & ":" & "E" & x).Width

Becomes -- without variables

.Height = Range("B2:B5").Height
.Width = Range("B2:E2").Width

The Height will fill from B2 down to B5

 

[carrguy01]

do i need to add the lock aspect ratio line when i want to put the log over the cell renge a11 to c11 wide and a11 to a13 hieght


ActiveSheet.Pictures.Insert ("C:\Users\Owner\Desktop\work\Marketing\Logo.jpg")
.LockAspectRatio = False
.Top = Range("a11").Top
.Left = Range("a11").Left
.Height = Range("a11:a13").Height
.Width = Range("a11:c11").Width
End With
End Sub

 

[carrguy01]

Sub piccy2()
Dim x As Long: x = 2
Dim y As Long: y = 9
ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\new4.jpg")
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("a2").Top
.Left = Range("a2").Left
.Height = Range("a" & x & ":" & "B" & y).Height
.Width = Range("a" & x & ":" & "c" & x).Width
End With
ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\85938.jpg")
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("i2").Top
.Left = Range("i2").Left
.Height = Range("F" & x & ":" & "F" & y).Height
.Width = Range("i" & x & ":" & "k" & x).Width
End With
ActiveSheet.Pictures.Insert ("C:\Users\Public\Pictures\work\373 Pulver Rd. (2110105) open house sun. 2 30-4. $324900.jpg")
With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
.LockAspectRatio = False
.Top = Range("f15").Top
.Left = Range("f15").Left
.Height = Range("f" & x & x & ":" & "g" & y).Height
.Width = Range("f" & x & ":" & "k" & x).Width
End With
ActiveSheet.Pictures.Insert ("C:\Users\Owner\Desktop\work\Marketing\Logo.jpg")
.Height = Range("a11:a13").Height
.Width = Range("a11:c11").Width
End With
End Sub

the last part does not work can you explain why i want the logo on cel a11 to a13 height and a11 to c11

 

[jeffreybrown]

You are missing the With part and .Top/.Left. Your code has four sections, four pictures, therefore, all sections should look the same.

ActiveSheet.Pictures.Insert("C:\Users\Owner\Desktop\work\Marketing\Logo.jpg")
.Height = Range("a11:a13").Height
.Width = Range("a11:c11").Width
End With

One tool you should download which could help you out to spot missing pieces in your code is the Smart Indenter. It is free

Also, when you post your code to the board it would help tremendously to add Code Tags

 

[carrguy01]

will they work for office 2010 the link does to 2003

 

[jeffreybrown]

Are you referring to the Smart Identer?

If so, don't have 2010 so not sure; however, works with 2007.