How to insert 3 images into excel with aligning each positions?

Does anyone have any suggestions on how to insert 3 images into excel with 
aligning each positions? Please see following code for detailed description.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPic As Object
Dim dblTop As Double
Dim dblLeft As Double
Dim dblHeight As Double
Dim dblWidth As Double

If Target.Address = "$A$1" Then
  On Error Resume Next
  Set myPic = ActiveSheet.Pictures(1)
  On Error GoTo 0
  If Not myPic Is Nothing Then myPic.Delete
  
  If Range("A1") = 1 Then
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
' I try to insert more code here, but it does not work
  Else
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
    Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
  End If
  
  dblTop = Cells(10, "B").Top
  dblLeft = Cells(10, "B").Left
  dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
  dblWidth = Cells(10, "D").Left - Cells(10, "B").Left
  
'Furthermore, I get no idea on how to align 3 different images from 
following code.
  With myPic
    .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
    .Top = dblTop
    .Left = dblLeft
    .Height = dblHeight
    .Width = dblWidth
  End With

End If

End Sub
0
Utf
2/10/2010 6:49:01 AM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
1360 Views

Similar Articles

[PageSpeed] 58

Hi again Eric,

You could have continued the tread you started before.

Tell us where you want each of the pictures. The first one you previously 
gave me as B10 to C13 inclusive for position and size. What position and size 
do you want each of these additional ones?
 
-- 
Regards,

OssieMac


"Eric" wrote:

> Does anyone have any suggestions on how to insert 3 images into excel with 
> aligning each positions? Please see following code for detailed description.
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
> 
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> Dim myPic As Object
> Dim dblTop As Double
> Dim dblLeft As Double
> Dim dblHeight As Double
> Dim dblWidth As Double
> 
> If Target.Address = "$A$1" Then
>   On Error Resume Next
>   Set myPic = ActiveSheet.Pictures(1)
>   On Error GoTo 0
>   If Not myPic Is Nothing Then myPic.Delete
>   
>   If Range("A1") = 1 Then
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
> ' I try to insert more code here, but it does not work
>   Else
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
>   End If
>   
>   dblTop = Cells(10, "B").Top
>   dblLeft = Cells(10, "B").Left
>   dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
>   dblWidth = Cells(10, "D").Left - Cells(10, "B").Left
>   
> 'Furthermore, I get no idea on how to align 3 different images from 
> following code.
>   With myPic
>     .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
> 
> End If
> 
> End Sub
0
Utf
2/10/2010 8:36:01 AM
Try one by one as below....


'Get the locations
  dblTop = Cells(10, "B").Top
  dblLeft = Cells(10, "B").Left
  dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
  dblWidth = Cells(10, "D").Left - Cells(10, "B").Left

'Insert picture
 Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")

'Set location
  With myPic
    .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
    .Top = dblTop
    .Left = dblLeft
    .Height = dblHeight
    .Width = dblWidth
  End With

-- 
Jacob


"Eric" wrote:

> Does anyone have any suggestions on how to insert 3 images into excel with 
> aligning each positions? Please see following code for detailed description.
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
> 
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> Dim myPic As Object
> Dim dblTop As Double
> Dim dblLeft As Double
> Dim dblHeight As Double
> Dim dblWidth As Double
> 
> If Target.Address = "$A$1" Then
>   On Error Resume Next
>   Set myPic = ActiveSheet.Pictures(1)
>   On Error GoTo 0
>   If Not myPic Is Nothing Then myPic.Delete
>   
>   If Range("A1") = 1 Then
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
> ' I try to insert more code here, but it does not work
>   Else
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
>     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
>   End If
>   
>   dblTop = Cells(10, "B").Top
>   dblLeft = Cells(10, "B").Left
>   dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
>   dblWidth = Cells(10, "D").Left - Cells(10, "B").Left
>   
> 'Furthermore, I get no idea on how to align 3 different images from 
> following code.
>   With myPic
>     .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
> 
> End If
> 
> End Sub
0
Utf
2/10/2010 8:37:01 AM
Thank everyone very much for suggestions
I would like to know the (1) within .Pictures, does 1 represent 1 image to 
be selected and deleted? If I have 3 images, then do I need to set (3) with 
..Pictures to perform the Delete function?
Thank everyone very much for any suggestions
Eric

  Set myPic = ActiveSheet.Pictures(1)
  On Error GoTo 0
  If Not myPic Is Nothing Then myPic.Delete


"Jacob Skaria" wrote:

> Try one by one as below....
> 
> 
> 'Get the locations
>   dblTop = Cells(10, "B").Top
>   dblLeft = Cells(10, "B").Left
>   dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
>   dblWidth = Cells(10, "D").Left - Cells(10, "B").Left
> 
> 'Insert picture
>  Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")
> 
> 'Set location
>   With myPic
>     .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
> 
> -- 
> Jacob
> 
> 
> "Eric" wrote:
> 
> > Does anyone have any suggestions on how to insert 3 images into excel with 
> > aligning each positions? Please see following code for detailed description.
> > Does anyone have any suggestions?
> > Thanks in advance for any suggestions
> > Eric
> > 
> > 
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 
> > Dim myPic As Object
> > Dim dblTop As Double
> > Dim dblLeft As Double
> > Dim dblHeight As Double
> > Dim dblWidth As Double
> > 
> > If Target.Address = "$A$1" Then
> >   On Error Resume Next
> >   Set myPic = ActiveSheet.Pictures(1)
> >   On Error GoTo 0
> >   If Not myPic Is Nothing Then myPic.Delete
> >   
> >   If Range("A1") = 1 Then
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic.JPG")
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
> > ' I try to insert more code here, but it does not work
> >   Else
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
> >     Set myPic = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
> >   End If
> >   
> >   dblTop = Cells(10, "B").Top
> >   dblLeft = Cells(10, "B").Left
> >   dblHeight = Cells(14, "B").Top - Cells(10, "B").Top
> >   dblWidth = Cells(10, "D").Left - Cells(10, "B").Left
> >   
> > 'Furthermore, I get no idea on how to align 3 different images from 
> > following code.
> >   With myPic
> >     .ShapeRange.LockAspectRatio = msoFalse    '/ msoTrue
> >     .Top = dblTop
> >     .Left = dblLeft
> >     .Height = dblHeight
> >     .Width = dblWidth
> >   End With
> > 
> > End If
> > 
> > End Sub
0
Utf
2/10/2010 9:14:01 AM
Hi again Eric,

I still don't know exactly how you want the pictures aligned (Horizontally 
or vertically). The following code aligns them horizontally with one cell in 
between. I have Used Range("B10") style addressing instead of Cells. Perhaps 
you can understand that better.

All of the alignment and sizing is based on the Top and Left position of 
cells.

I have created names for the shapes so they relate to the top left cell of 
each picture. The pictures must be named at the time of inserting so that 
they can be referred to again like when deleting. You cannot simply use the 
Picture index like Picture(1) because that refers to the first picture on the 
sheet irrespective of what it is. If you delete Picture(1) then what was 
Picture(2) becomes Picture(1). Once named the name does not change and can be 
used to reference the picture.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPic1 As Object
Dim myPic2 As Object
Dim myPic3 As Object
Dim dblTop As Double
Dim dblLeft As Double
Dim dblHeight As Double
Dim dblWidth As Double

If Target.Address = "$A$1" Then
  On Error Resume Next
  Set myPic1 = ActiveSheet.Pictures("PicAtB10")
  Set myPic2 = ActiveSheet.Pictures("PicAtE10")
  Set myPic3 = ActiveSheet.Pictures("PicAtH10")
  On Error GoTo 0
  If Not myPic1 Is Nothing Then myPic1.Delete
  If Not myPic2 Is Nothing Then myPic2.Delete
  If Not myPic3 Is Nothing Then myPic3.Delete
  
  If Range("A1") = 1 Then
    Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic1.JPG")
    Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
    Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
  Else
    Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
    Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
    Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
  End If

'Name and align myPic1 (Cells B10 to C13)

  myPic1.Name = "PicAtB10"
  
  dblTop = Range("B10").Top
  dblLeft = Range("B10").Left
  dblHeight = Range("B14").Top - Range("B10").Top
  dblWidth = Range("D10").Left - Range("B10").Left
  
  With myPic1
    .ShapeRange.LockAspectRatio = msoFalse
    .Top = dblTop
    .Left = dblLeft
    .Height = dblHeight
    .Width = dblWidth
  End With
  
'Name and align myPic2 (Cells E10 to F13)

  myPic2.Name = "PicAtE10"
  
  dblTop = Range("E10").Top
  dblLeft = Range("E10").Left
  dblHeight = Range("E14").Top - Range("E10").Top
  dblWidth = Range("G10").Left - Range("E10").Left
  
  With myPic2
    .ShapeRange.LockAspectRatio = msoFalse
    .Top = dblTop
    .Left = dblLeft
    .Height = dblHeight
    .Width = dblWidth
  End With
  
'Name and align myPic3 (Cells H3 to I13)

  myPic3.Name = "PicAtH10"
  
  dblTop = Range("H10").Top
  dblLeft = Range("H10").Left
  dblHeight = Range("H14").Top - Range("H10").Top
  dblWidth = Range("J10").Left - Range("H10").Left
  
  With myPic3
    .ShapeRange.LockAspectRatio = msoFalse
    .Top = dblTop
    .Left = dblLeft
    .Height = dblHeight
    .Width = dblWidth
  End With

End If

End Sub

-- 
Regards,

OssieMac


0
Utf
2/10/2010 11:21:02 AM
Thank everyone very very much for suggestions
Eric

"OssieMac" wrote:

> Hi again Eric,
> 
> I still don't know exactly how you want the pictures aligned (Horizontally 
> or vertically). The following code aligns them horizontally with one cell in 
> between. I have Used Range("B10") style addressing instead of Cells. Perhaps 
> you can understand that better.
> 
> All of the alignment and sizing is based on the Top and Left position of 
> cells.
> 
> I have created names for the shapes so they relate to the top left cell of 
> each picture. The pictures must be named at the time of inserting so that 
> they can be referred to again like when deleting. You cannot simply use the 
> Picture index like Picture(1) because that refers to the first picture on the 
> sheet irrespective of what it is. If you delete Picture(1) then what was 
> Picture(2) becomes Picture(1). Once named the name does not change and can be 
> used to reference the picture.
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> Dim myPic1 As Object
> Dim myPic2 As Object
> Dim myPic3 As Object
> Dim dblTop As Double
> Dim dblLeft As Double
> Dim dblHeight As Double
> Dim dblWidth As Double
> 
> If Target.Address = "$A$1" Then
>   On Error Resume Next
>   Set myPic1 = ActiveSheet.Pictures("PicAtB10")
>   Set myPic2 = ActiveSheet.Pictures("PicAtE10")
>   Set myPic3 = ActiveSheet.Pictures("PicAtH10")
>   On Error GoTo 0
>   If Not myPic1 Is Nothing Then myPic1.Delete
>   If Not myPic2 Is Nothing Then myPic2.Delete
>   If Not myPic3 Is Nothing Then myPic3.Delete
>   
>   If Range("A1") = 1 Then
>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic1.JPG")
>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
>   Else
>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
>   End If
> 
> 'Name and align myPic1 (Cells B10 to C13)
> 
>   myPic1.Name = "PicAtB10"
>   
>   dblTop = Range("B10").Top
>   dblLeft = Range("B10").Left
>   dblHeight = Range("B14").Top - Range("B10").Top
>   dblWidth = Range("D10").Left - Range("B10").Left
>   
>   With myPic1
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
>   
> 'Name and align myPic2 (Cells E10 to F13)
> 
>   myPic2.Name = "PicAtE10"
>   
>   dblTop = Range("E10").Top
>   dblLeft = Range("E10").Left
>   dblHeight = Range("E14").Top - Range("E10").Top
>   dblWidth = Range("G10").Left - Range("E10").Left
>   
>   With myPic2
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
>   
> 'Name and align myPic3 (Cells H3 to I13)
> 
>   myPic3.Name = "PicAtH10"
>   
>   dblTop = Range("H10").Top
>   dblLeft = Range("H10").Left
>   dblHeight = Range("H14").Top - Range("H10").Top
>   dblWidth = Range("J10").Left - Range("H10").Left
>   
>   With myPic3
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
> 
> End If
> 
> End Sub
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
2/10/2010 2:11:02 PM
Does anyone have any suggestions on how to solve the triggering issue?
The value within cell A1 is calculated by formula, so I don't need to 
manually press enter everytime to update this value, the question is the rest 
of coding will not be performed without triggering cell A1, so does anyone 
have any suggestions on how to trigger the rest of coding without manually 
update the A1 cell's value?
Thank everyone very much for any suggestions
Eric

If Target.Address = "$A$1" Then
  On Error Resume Next
  Set myPic1 = ActiveSheet.Pictures("PicAtB10")
  Set myPic2 = ActiveSheet.Pictures("PicAtE10")
  Set myPic3 = ActiveSheet.Pictures("PicAtH10")
  On Error GoTo 0
  If Not myPic1 Is Nothing Then myPic1.Delete
  If Not myPic2 Is Nothing Then myPic2.Delete
  If Not myPic3 Is Nothing Then myPic3.Delete


"OssieMac" wrote:

> Hi again Eric,
> 
> I still don't know exactly how you want the pictures aligned (Horizontally 
> or vertically). The following code aligns them horizontally with one cell in 
> between. I have Used Range("B10") style addressing instead of Cells. Perhaps 
> you can understand that better.
> 
> All of the alignment and sizing is based on the Top and Left position of 
> cells.
> 
> I have created names for the shapes so they relate to the top left cell of 
> each picture. The pictures must be named at the time of inserting so that 
> they can be referred to again like when deleting. You cannot simply use the 
> Picture index like Picture(1) because that refers to the first picture on the 
> sheet irrespective of what it is. If you delete Picture(1) then what was 
> Picture(2) becomes Picture(1). Once named the name does not change and can be 
> used to reference the picture.
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> Dim myPic1 As Object
> Dim myPic2 As Object
> Dim myPic3 As Object
> Dim dblTop As Double
> Dim dblLeft As Double
> Dim dblHeight As Double
> Dim dblWidth As Double
> 
> If Target.Address = "$A$1" Then
>   On Error Resume Next
>   Set myPic1 = ActiveSheet.Pictures("PicAtB10")
>   Set myPic2 = ActiveSheet.Pictures("PicAtE10")
>   Set myPic3 = ActiveSheet.Pictures("PicAtH10")
>   On Error GoTo 0
>   If Not myPic1 Is Nothing Then myPic1.Delete
>   If Not myPic2 Is Nothing Then myPic2.Delete
>   If Not myPic3 Is Nothing Then myPic3.Delete
>   
>   If Range("A1") = 1 Then
>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic1.JPG")
>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
>   Else
>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
>   End If
> 
> 'Name and align myPic1 (Cells B10 to C13)
> 
>   myPic1.Name = "PicAtB10"
>   
>   dblTop = Range("B10").Top
>   dblLeft = Range("B10").Left
>   dblHeight = Range("B14").Top - Range("B10").Top
>   dblWidth = Range("D10").Left - Range("B10").Left
>   
>   With myPic1
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
>   
> 'Name and align myPic2 (Cells E10 to F13)
> 
>   myPic2.Name = "PicAtE10"
>   
>   dblTop = Range("E10").Top
>   dblLeft = Range("E10").Left
>   dblHeight = Range("E14").Top - Range("E10").Top
>   dblWidth = Range("G10").Left - Range("E10").Left
>   
>   With myPic2
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
>   
> 'Name and align myPic3 (Cells H3 to I13)
> 
>   myPic3.Name = "PicAtH10"
>   
>   dblTop = Range("H10").Top
>   dblLeft = Range("H10").Left
>   dblHeight = Range("H14").Top - Range("H10").Top
>   dblWidth = Range("J10").Left - Range("H10").Left
>   
>   With myPic3
>     .ShapeRange.LockAspectRatio = msoFalse
>     .Top = dblTop
>     .Left = dblLeft
>     .Height = dblHeight
>     .Width = dblWidth
>   End With
> 
> End If
> 
> End Sub
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
2/11/2010 8:46:01 AM
hi Eric

perhaps you could make the formula in A1 volatile by adding and subtracting 
NOW()

=your_formula+NOW()-NOW()

-- 
-------
Regards
Roger Govier

"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:EA038C47-AD84-464A-A980-BEC488243FA6@microsoft.com...
> Does anyone have any suggestions on how to solve the triggering issue?
> The value within cell A1 is calculated by formula, so I don't need to
> manually press enter everytime to update this value, the question is the 
> rest
> of coding will not be performed without triggering cell A1, so does anyone
> have any suggestions on how to trigger the rest of coding without manually
> update the A1 cell's value?
> Thank everyone very much for any suggestions
> Eric
>
> If Target.Address = "$A$1" Then
>  On Error Resume Next
>  Set myPic1 = ActiveSheet.Pictures("PicAtB10")
>  Set myPic2 = ActiveSheet.Pictures("PicAtE10")
>  Set myPic3 = ActiveSheet.Pictures("PicAtH10")
>  On Error GoTo 0
>  If Not myPic1 Is Nothing Then myPic1.Delete
>  If Not myPic2 Is Nothing Then myPic2.Delete
>  If Not myPic3 Is Nothing Then myPic3.Delete
>
>
> "OssieMac" wrote:
>
>> Hi again Eric,
>>
>> I still don't know exactly how you want the pictures aligned 
>> (Horizontally
>> or vertically). The following code aligns them horizontally with one cell 
>> in
>> between. I have Used Range("B10") style addressing instead of Cells. 
>> Perhaps
>> you can understand that better.
>>
>> All of the alignment and sizing is based on the Top and Left position of
>> cells.
>>
>> I have created names for the shapes so they relate to the top left cell 
>> of
>> each picture. The pictures must be named at the time of inserting so that
>> they can be referred to again like when deleting. You cannot simply use 
>> the
>> Picture index like Picture(1) because that refers to the first picture on 
>> the
>> sheet irrespective of what it is. If you delete Picture(1) then what was
>> Picture(2) becomes Picture(1). Once named the name does not change and 
>> can be
>> used to reference the picture.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> Dim myPic1 As Object
>> Dim myPic2 As Object
>> Dim myPic3 As Object
>> Dim dblTop As Double
>> Dim dblLeft As Double
>> Dim dblHeight As Double
>> Dim dblWidth As Double
>>
>> If Target.Address = "$A$1" Then
>>   On Error Resume Next
>>   Set myPic1 = ActiveSheet.Pictures("PicAtB10")
>>   Set myPic2 = ActiveSheet.Pictures("PicAtE10")
>>   Set myPic3 = ActiveSheet.Pictures("PicAtH10")
>>   On Error GoTo 0
>>   If Not myPic1 Is Nothing Then myPic1.Delete
>>   If Not myPic2 Is Nothing Then myPic2.Delete
>>   If Not myPic3 Is Nothing Then myPic3.Delete
>>
>>   If Range("A1") = 1 Then
>>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic1.JPG")
>>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
>>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
>>   Else
>>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
>>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
>>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
>>   End If
>>
>> 'Name and align myPic1 (Cells B10 to C13)
>>
>>   myPic1.Name = "PicAtB10"
>>
>>   dblTop = Range("B10").Top
>>   dblLeft = Range("B10").Left
>>   dblHeight = Range("B14").Top - Range("B10").Top
>>   dblWidth = Range("D10").Left - Range("B10").Left
>>
>>   With myPic1
>>     .ShapeRange.LockAspectRatio = msoFalse
>>     .Top = dblTop
>>     .Left = dblLeft
>>     .Height = dblHeight
>>     .Width = dblWidth
>>   End With
>>
>> 'Name and align myPic2 (Cells E10 to F13)
>>
>>   myPic2.Name = "PicAtE10"
>>
>>   dblTop = Range("E10").Top
>>   dblLeft = Range("E10").Left
>>   dblHeight = Range("E14").Top - Range("E10").Top
>>   dblWidth = Range("G10").Left - Range("E10").Left
>>
>>   With myPic2
>>     .ShapeRange.LockAspectRatio = msoFalse
>>     .Top = dblTop
>>     .Left = dblLeft
>>     .Height = dblHeight
>>     .Width = dblWidth
>>   End With
>>
>> 'Name and align myPic3 (Cells H3 to I13)
>>
>>   myPic3.Name = "PicAtH10"
>>
>>   dblTop = Range("H10").Top
>>   dblLeft = Range("H10").Left
>>   dblHeight = Range("H14").Top - Range("H10").Top
>>   dblWidth = Range("J10").Left - Range("H10").Left
>>
>>   With myPic3
>>     .ShapeRange.LockAspectRatio = msoFalse
>>     .Top = dblTop
>>     .Left = dblLeft
>>     .Height = dblHeight
>>     .Width = dblWidth
>>   End With
>>
>> End If
>>
>> End Sub
>>
>> -- 
>> Regards,
>>
>> OssieMac
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4856 (20100210) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4856 (20100210) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
2/11/2010 9:10:04 AM
I have tried it, but it does not work, do you have any more suggestions?
Thank everyone very much for any suggestions
Eric

"Roger Govier" wrote:

> hi Eric
> 
> perhaps you could make the formula in A1 volatile by adding and subtracting 
> NOW()
> 
> =your_formula+NOW()-NOW()
> 
> -- 
> -------
> Regards
> Roger Govier
> 
> "Eric" <Eric@discussions.microsoft.com> wrote in message 
> news:EA038C47-AD84-464A-A980-BEC488243FA6@microsoft.com...
> > Does anyone have any suggestions on how to solve the triggering issue?
> > The value within cell A1 is calculated by formula, so I don't need to
> > manually press enter everytime to update this value, the question is the 
> > rest
> > of coding will not be performed without triggering cell A1, so does anyone
> > have any suggestions on how to trigger the rest of coding without manually
> > update the A1 cell's value?
> > Thank everyone very much for any suggestions
> > Eric
> >
> > If Target.Address = "$A$1" Then
> >  On Error Resume Next
> >  Set myPic1 = ActiveSheet.Pictures("PicAtB10")
> >  Set myPic2 = ActiveSheet.Pictures("PicAtE10")
> >  Set myPic3 = ActiveSheet.Pictures("PicAtH10")
> >  On Error GoTo 0
> >  If Not myPic1 Is Nothing Then myPic1.Delete
> >  If Not myPic2 Is Nothing Then myPic2.Delete
> >  If Not myPic3 Is Nothing Then myPic3.Delete
> >
> >
> > "OssieMac" wrote:
> >
> >> Hi again Eric,
> >>
> >> I still don't know exactly how you want the pictures aligned 
> >> (Horizontally
> >> or vertically). The following code aligns them horizontally with one cell 
> >> in
> >> between. I have Used Range("B10") style addressing instead of Cells. 
> >> Perhaps
> >> you can understand that better.
> >>
> >> All of the alignment and sizing is based on the Top and Left position of
> >> cells.
> >>
> >> I have created names for the shapes so they relate to the top left cell 
> >> of
> >> each picture. The pictures must be named at the time of inserting so that
> >> they can be referred to again like when deleting. You cannot simply use 
> >> the
> >> Picture index like Picture(1) because that refers to the first picture on 
> >> the
> >> sheet irrespective of what it is. If you delete Picture(1) then what was
> >> Picture(2) becomes Picture(1). Once named the name does not change and 
> >> can be
> >> used to reference the picture.
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>
> >> Dim myPic1 As Object
> >> Dim myPic2 As Object
> >> Dim myPic3 As Object
> >> Dim dblTop As Double
> >> Dim dblLeft As Double
> >> Dim dblHeight As Double
> >> Dim dblWidth As Double
> >>
> >> If Target.Address = "$A$1" Then
> >>   On Error Resume Next
> >>   Set myPic1 = ActiveSheet.Pictures("PicAtB10")
> >>   Set myPic2 = ActiveSheet.Pictures("PicAtE10")
> >>   Set myPic3 = ActiveSheet.Pictures("PicAtH10")
> >>   On Error GoTo 0
> >>   If Not myPic1 Is Nothing Then myPic1.Delete
> >>   If Not myPic2 Is Nothing Then myPic2.Delete
> >>   If Not myPic3 Is Nothing Then myPic3.Delete
> >>
> >>   If Range("A1") = 1 Then
> >>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic1.JPG")
> >>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic3.JPG")
> >>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic5.JPG")
> >>   Else
> >>     Set myPic1 = ActiveSheet.Pictures.Insert("C:\TempPic2.JPG")
> >>     Set myPic2 = ActiveSheet.Pictures.Insert("C:\TempPic4.JPG")
> >>     Set myPic3 = ActiveSheet.Pictures.Insert("C:\TempPic6.JPG")
> >>   End If
> >>
> >> 'Name and align myPic1 (Cells B10 to C13)
> >>
> >>   myPic1.Name = "PicAtB10"
> >>
> >>   dblTop = Range("B10").Top
> >>   dblLeft = Range("B10").Left
> >>   dblHeight = Range("B14").Top - Range("B10").Top
> >>   dblWidth = Range("D10").Left - Range("B10").Left
> >>
> >>   With myPic1
> >>     .ShapeRange.LockAspectRatio = msoFalse
> >>     .Top = dblTop
> >>     .Left = dblLeft
> >>     .Height = dblHeight
> >>     .Width = dblWidth
> >>   End With
> >>
> >> 'Name and align myPic2 (Cells E10 to F13)
> >>
> >>   myPic2.Name = "PicAtE10"
> >>
> >>   dblTop = Range("E10").Top
> >>   dblLeft = Range("E10").Left
> >>   dblHeight = Range("E14").Top - Range("E10").Top
> >>   dblWidth = Range("G10").Left - Range("E10").Left
> >>
> >>   With myPic2
> >>     .ShapeRange.LockAspectRatio = msoFalse
> >>     .Top = dblTop
> >>     .Left = dblLeft
> >>     .Height = dblHeight
> >>     .Width = dblWidth
> >>   End With
> >>
> >> 'Name and align myPic3 (Cells H3 to I13)
> >>
> >>   myPic3.Name = "PicAtH10"
> >>
> >>   dblTop = Range("H10").Top
> >>   dblLeft = Range("H10").Left
> >>   dblHeight = Range("H14").Top - Range("H10").Top
> >>   dblWidth = Range("J10").Left - Range("H10").Left
> >>
> >>   With myPic3
> >>     .ShapeRange.LockAspectRatio = msoFalse
> >>     .Top = dblTop
> >>     .Left = dblLeft
> >>     .Height = dblHeight
> >>     .Width = dblWidth
> >>   End With
> >>
> >> End If
> >>
> >> End Sub
> >>
> >> -- 
> >> Regards,
> >>
> >> OssieMac
> >>
> >>
> >
> > __________ Information from ESET Smart Security, version of virus 
> > signature database 4856 (20100210) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> > 
> 
> __________ Information from ESET Smart Security, version of virus signature database 4856 (20100210) __________
> 
> The message was checked by ESET Smart Security.
> 
> http://www.eset.com
> 
> 
> 
> .
> 
0
Utf
2/11/2010 12:16:01 PM
Reply:

Similar Artilces:

How to open MS Excell 2007 Sheet in MS Excell 2000???
Hello, All! Is anybody know any converter for this trouble or how to . . .??? With best regards, Alek Luchnikov. E-mail: alekluch---FORSPAMERS---1983@mail.ru REMOVE ---FORSPAMERS--- Alek Luchnikov wrote: > Hello, All! > > Is anybody know any converter for this trouble or how to . . .??? > > With best regards, Alek Luchnikov. > E-mail: alekluch---FORSPAMERS---1983@mail.ru REMOVE ---FORSPAMERS--- > > Search for Microsoft Office Compatibility Pack. It should have prompted you to download it when you open the doc. Once it's installed you will be able to vi...

split screen #3
Hello, I want to have a chart on one side of the window that does not move and the data in columns on the other side that does. I split the screen, but both sides move when I move down the worksheet. Thanks. Bill Bill, go to the the Menu bar, and 1-Window > New Window. 2-Window > Arrange > Vertical > Windows of Active Workbook > OK. You will get your workbook in side-by-side windows, each navigable independently of the other. Pretty neat, huh? DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "Bill" <whmc2@ix.netcom.com> wr...

Excel '97
Is it possible to purchas an original install copy of Excel '97 anymore? Hi try ebay -- Regards Frank Kabel Frankfurt, Germany "Jeanne" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:003401c4f287$24f9e480$a401280a@phx.gbl... > Is it possible to purchas an original install copy of > Excel '97 anymore? I've tried Amazon and Ebay, and they both have UPGRADES available, but the original Office package wasn't part of the 97 OS was it? >-----Original Message----- >Hi >try ebay > >-- >Regards >Frank Kabel >F...

How do I get the document to align with my labels?
I keep trying to print these labels, and they end up wasting because the alignment is off. I am using the right number labels along with the application, but for some reason it's not working. My printer is a F335 All-In-One. Please advise. Thank You. Extremely difficult without seeing them. But 99% of the time it is because normal.dot has been modified by adding a Header or Footer or similar and labels are based on normal.dot. So try starting Word in Safe Mode and create a sheet of labels to see if that works. If it does, rename normal.dot as normal.bad and start Word agai...

Export Access Record to Specific Cells in Excel
I'm looking for suggestions on how to export data from an Access table or Query into specific cells of a Worksheet. The table I'm importing will always contain a single record only. For example, I'd like to export data from Field1, field2...etc in an access table to Sheet2!A3, Sheet2!B3...etc. Any sugestions would be most appreciative. ...

Macro Security #3
In the process of building an Excel spreadsheet, I added a couple of macros. (Mistake #1, I know.) I don't really need the macros, so I deleted them and saved the file. But now when I go to re-open the file, I get that annoying little "Macros may contain viruses, it is always safe to disable macros, but if the macros are legitimate, blah, blah, blah..." dialog box. (There are no macros listed in the Macro dialog box when I do the ol' Alt+F8.) I know I can reset my security level to high and not have to deal with this, but I don't want to do that; I want Excel to recogn...

Excel to publisher import
How can I import an excel spreadsheet to publisher doc.? If you want to insert the actual spreadsheet, you can go to Insert > Object > Create from file and browse to the Excel file. If you want to merge the contents of the cells, you can use the Mail Merge feature in Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Bill" <Bill@discussions.microsoft.com> wrote in message news:80F32E78-1710-40E9-8922-CE4EBD69F5C2@microsoft.com... > How can I imp...

Excel 2007 and Word 2007 Hangs when using the File -> Open command
I have a couple users that when they have Excel 2007 or Word 2007 open, and click the File -> Open option, and choose a file, it either takes about 3 minutes to open the file or the program will crash. If they were to double click on the documents or spreadsheets, the doc or sheet will open right up. This is happening on the user's Hard drive as well as network drives. Once you get a file opened in Word or Excel, however, the next time you go to File -> Open, it's really fast to open the file. ...

Saving chart as an image file
I want to create a pie chart in Microsoft Excel, then save it as an image file (.jpg or .gif) so I can put it on a website. How do I do this? Secondly, as I create my pie chart, how do I set my data labels? I want to change them from the default (numbers) to my own description for each slice. Jon Peltier has some instructions in his Excel Charting FAQ article for exporting a chart in gif format: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon008 To add your own labels to the chart, you can use Rob Bovey's XY Chart Labeler. It's a free add-in that yo...

Analysing data from several excel workbooks
Hi, I'm totally new to excel and i need to analyse the changes in a funding position across several years in a seperate excel sheet. As in, submissions are made year on year showing the amount of revenue spent on several different services. I need to be able to compare how those have moved over the last 5 years in a single spreadsheet. I appreciate any help. Thanks To give a clear answer we would need a bit more detail of the data layout Give us a simplified version of what the data looks like Your 'subject' talks about different workBOOKS but the text of the ...

Insert Macro
I've just seen a macro that I'd like to add into my current macro. Do I simply copy and paste to the bottom of my current macro? Thanks! If you're lucky, that would work. You may have to adjust variable names or even declare variable. Another way is to keep that macro as a different subroutine and just call it: sub yourexistingmacro() 'all your existing code call yournewmacro end sub sub yournewmacro() 'all that newstuff end sub Change "yournewmacro" to the correct name in both spots. And save your workbook before you test it. If it blows up real good...

Too Many Excel Apps running
I recently installed Excel 2010. My operating system is XP Pro. Ever since I installed Excel 2010 I find that every time I open an Excel file it opens a new Excel. It is not unusual for me to have 5 different Excels open even after I close a file. My bottom ribbon is full of Excels! I previously used Excel 2003 and did not have this problem. I know these are different Excels operating because I am unable to open a split window to show two different files. Note - I usually open my files via Explorer. Is there a setting that I can use to require that my single open Excel can open any new Exce...

Excel document changes format
Hi, I have an Excel document that changes format unexpectedly. It seems to change the format of the document eventhough i saved it. I have to resize the windows of the bar graphs, etc. It just doesn't save it the way i want. I re-installed MSOffice and re-did the NT profile, but still persists. Any ideas? Thanks. Excel does seem to have problems if the screen zoom is anything othe than 100%. Usually changing to 100 and back resets he screen display -- Message posted from http://www.ExcelForum.com ...

freeform image
Can you make an image freeform? I am making a custom border and would like to bend my image in the middle at the corners. Publisher 2003, sm business ed, XP What format is the image? If it is a bitmap you probably could do some edits with crop and rotate. It it is a vector (.wmf) you could ungroup and do some edits. The best is a draw program: Serif has some free applications. http://www.freeserifsoftware.com/ -- Mary Sauer http://msauer.mvps.org/ "A_Ross" <ARoss@discussions.microsoft.com> wrote in message news:E64B3799-6C1C-4164-B388-B0D21FDFC826@microsoft.com... >...

Business Portal 3.0 Breaks SQL Reporting Services
Hi, I had a box with Windows 2003 Server, running SQL 2005 with SQL Reporting Services up and running fine. SharePoint was also installed and running fine. Then I installed Business Portal and now SharePoint works, BP works but SQL Reporting Services is broke. I have done an uninstal and reinstall of SQL Reporting Services. Anyone see the error below or know what I should change? Server Error in '/Reports' Application Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the speci...

Linking Word and Excel #2
I have a Word document with a large number of tables linked to cells in an Excel spreadsheet. The tables in the worksheet are in the same order as those in the Word document. I would like to insert a new table between other tables linked to the Excel spreadsheet but when I insert the new table between the others in the Excel spreadsheet, it invalidates all the other linked information. I take this to mean that the links are not absolute references. Is there any way to do this without having to reconstruct the links? Thanks ...

can you date time stamp entries in excel
is it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Private Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, "dd mmm yyy hh:mm") End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate ...

Excell 2007 messing up graphics
I have an invoice Excel sheet setup with my company logo on it. For some reason Excel 2007 messes it up during printing. It prints it about 10 times the size. I tried to re create the work sheet and it still will not print the graphic. It show it ok but will not print it. When I go to Print Preview the logo (JPG graphing) is not seen at all. Any Ideas what is going on? ...

VFW webcam
Hi, I'm using Video For Windows to capture images from creative webcam in my application. This is giving me image in size 640x480. However, i need image in landscape resolution where width is less than height. I tried to squeeze the captured image but its distorting it badly. Any ideas how can we capture images from webcam in desired resolution? Thanks, neo see the amcap sample in DirectShow SDK -- Regards Sheng Jiang Microsoft Most Valuable Professional in Visual C++ http://www.jiangsheng.net http://blog.joycode.com/jiangsheng/ <dhapola@yahoo.com> ???? news:1160125564.284431....

To get user training manual for Microsoft CRM 3.0
Hello I am implementing Microsoft CRM 3.0 for an health care company in Michigan. Right now I have experience with installation, customizing and CRM application functions for 2 years. In the future I would like to pursue my future as a Microsoft CRM consultant. Thus, I would like to get all 3 of certificates of Microsoft CRM:- Which is:- 1. Application 2. Customizing 3. Installation and Configuration The problem I have is I work for a company which I am not permanent employee, and this company is not Microsoft Partner also. So If I would like to have user manuals for preparation to ...

meeting request #3
How is it possible that when I am sending a meetingrequest to someone I receive an Out of Office reply from somebody else? We are using Outlook2000 in Exchange server 5.5 SP3 Thanks. John ...

I can't see the image picture from the manager program
Using the Manager - Item Properties - Special Tab You can select the picture files that reside in the associated folder that is set by the Adminastrator - paths - pictures - folder location. The files sit on the backoffice PC and I can see the selected picture in the little window, via the Manager program no problem. At the Inventory PC, I set the Admin program to look at the server and file paths are set. I can see the picture files and select them but no image shows up in the little window. Anybody got any ideas what is causing this? Moe Register is looking local for photos, needs to ...

how do i report a bug in excel
how do i report a bug in excel Hi Dave well if you'ld like to post here what you've found we can (probably) tell you if it is a known one already and maybe even suggest a solution or workaround. Cheers JulieD "dave" <dave@discussions.microsoft.com> wrote in message news:DF36F937-24C7-4E2C-A4FF-D743A4E9AAC0@microsoft.com... > how do i report a bug in excel Or, as is often the case, not a bug at all but "just the way things work". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "JulieD&q...

spellnumber #3
The help i requested for number to to be spelled in indian rupees , i have the excel vba code but do not how to get it run in excel , code as below hope you can help me . Sub Macro4() words(0) = "Zero" words(1) = "One" words(2) = "Two" words(3) = "Three" words(4) = "Four" words(5) = "Five" words(6) = "Six" words(7) = "Seven" words(8) = "Eight" words(9) = "Nine" words(10) = "Ten" words(11) = "Eleven" words(12) = "Twelve" words(13) = "Thirteen" words(14)...

Excel & PDF
With Excel 2000 there was a PDF conversion function. Now I have Excel 2003 and there is no PDF converter. Is this right? I don't have a PDF conversion at work (xl2k). You sure your conversion software didn't come separate and installed itself as an option in excel (maybe with your Scanner/OCR???). Bob Purcell wrote: > > With Excel 2000 there was a PDF conversion function. Now > I have Excel 2003 and there is no PDF converter. Is this > right? -- Dave Peterson ec35720@msn.com ...