Below is my VBA script to dynamically insert and size an image to a merged
cell.

My questions are, can I:
- scale the image to maximum inside a merged cell, while keeping its
original height to width ratio (instead of distorting the image scale to the
full merge cell size as it does now)?
- change the caption of the command button to "edit image" if an image has
been inserted, and change back to "insert image" if nothing is inserted?

Any help would be appreciated :-)

-------------------------------------------------
Private Sub CommandButton_Click()

Dim ImageCell As Range
Set ImageCell = Sheet3.Range("B10").MergeArea

' Go to "screen dump" input merged cell (B10:AK30)
ImageCell.Select

' Open "Insert Picture" pop-up window
Application.Dialogs(xlDialogInsertPicture).Show

' Quite action if nothing is selected
On Error Resume Next

' Otherwise size the image selection to full merged cell size
With Selection
.Left = ImageCell.Left
.Top = ImageCell.Top
.Width = ImageCell.Width
.Height = ImageCell.Height
.Placement = xlMoveAndSize
End With

End Sub

Dimensions of merged area: ImageCell.MergeArea.Width etc.

To scale without changing the aspect ratio, calculate the ratios of [pic
width/range width] and [pic height/range height]. Use whichever ration is
appropriate to scale *both dimensions* of the picture.


Tim



"Sam Kuo" wrote in message
...
Below is my VBA script to dynamically insert and size an image to a merged
cell.

My questions are, can I:
- scale the image to maximum inside a merged cell, while keeping its
original height to width ratio (instead of distorting the image scale to
the
full merge cell size as it does now)?
- change the caption of the command button to "edit image" if an image has
been inserted, and change back to "insert image" if nothing is inserted?

Any help would be appreciated :-)

-------------------------------------------------
Private Sub CommandButton_Click()

Dim ImageCell As Range
Set ImageCell = Sheet3.Range("B10").MergeArea

' Go to "screen dump" input merged cell (B10:AK30)
ImageCell.Select

' Open "Insert Picture" pop-up window
Application.Dialogs(xlDialogInsertPicture).Show

' Quite action if nothing is selected
On Error Resume Next

' Otherwise size the image selection to full merged cell size
With Selection
.Left = ImageCell.Left
.Top = ImageCell.Top
.Width = ImageCell.Width
.Height = ImageCell.Height
.Placement = xlMoveAndSize
End With

End Sub

Thanks Tim. I was thinking of the same plan of attack, but don't know how to
execute in VBA. It'd be much appreciated if you (or anyone) can help...

In addition to the above questions (yes I know it's frustrating), I'd also
need to be able to delete the existing picture (if any) before inserting the
new one, so that only one image is shown at all time. Any idea how to do this
in VBA??

Many thanks!


"Tim Williams" wrote:

Dimensions of merged area: ImageCell.MergeArea.Width etc.

To scale without changing the aspect ratio, calculate the ratios of [pic
width/range width] and [pic height/range height]. Use whichever ration is
appropriate to scale *both dimensions* of the picture.


Tim



"Sam Kuo" wrote in message
...
Below is my VBA script to dynamically insert and size an image to a merged
cell.

My questions are, can I:
- scale the image to maximum inside a merged cell, while keeping its
original height to width ratio (instead of distorting the image scale to
the
full merge cell size as it does now)?
- change the caption of the command button to "edit image" if an image has
been inserted, and change back to "insert image" if nothing is inserted?

Any help would be appreciated :-)

-------------------------------------------------
Private Sub CommandButton_Click()

Dim ImageCell As Range
Set ImageCell = Sheet3.Range("B10").MergeArea

' Go to "screen dump" input merged cell (B10:AK30)
ImageCell.Select

' Open "Insert Picture" pop-up window
Application.Dialogs(xlDialogInsertPicture).Show

' Quite action if nothing is selected
On Error Resume Next

' Otherwise size the image selection to full merged cell size
With Selection
.Left = ImageCell.Left
.Top = ImageCell.Top
.Width = ImageCell.Width
.Height = ImageCell.Height
.Placement = xlMoveAndSize
End With

End Sub