Image auto re-sized when inserted

[excelrukie]

I am a superintendent for a construction company and I have a workbook that I am currently working on and I am fairly new to excel. It is for daily reports in which I have to add 4 pictures everyday for 6 days a week. I found that if I use a chart it will auto re-size pictures for me cutting down my work tremendously, but I also have a lot of other objects on the sheet that I dont want to accidently grab and move.

When I protect the sheet I no longer have the ability to change the images and yes I have unlocked the chart and the cells behind. I dont have to use the chart other than the fact that it is the only thing I have found to auto resize pictures.

Please keep in mind that this workbook will be for other superintendents to use as well as myself and we usally have 80-90hrs of work a week other than filling out these daily reports.

 

[Yongle]

Consider using VBA to resize pictures
- here is a simple example to illustrate
- procedure amends the height of image and moves it to a cell specified by user

Test in a NEW workbook by doing the following ..

1. Add the code like this
- go to VBA editor with shortcaut {ALT}{F11}
- Insert a Module with shortcut {ALT} I M
- paste code below into that module
- go back to Excel with shortcaut {ALT}{F11}

Sub Resize()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(Application.Caller)
    shp.Height = 100
    With Application.InputBox("select cell", "Relocate image", , , , , , 8)
        shp.Left = .Left
        shp.Top = .Top
    End With
End Sub

2. Insert an image anywhere in sheet1

3. Right-click on image \ click Assign Macro \ select macro Resize \ click OK

4. Click anywhere in sheet to deselect image

5. Click on image to run the macro

6. (In response to input box) select a cell in sheet1 and click OK

7. The image is resized and moved to selected cell

If above is helpful, then come back for more help
We need to know more about what you want to happen when an image is resized and moved
- does image need resizing when inserted ?
- are all images resized to the same height ?
- how is placement determined ?
etc

 

[excelrukie]

Thank you Yongle

The instructions you gave me were great but i can get the same effect by just inserting a picture and unlocking it. At least for what i am trying to do. The problem I have with that is that the pictures we will be inserting will be different sizes and when inserting them they resize but still dont all stay the same size. What I am looking for is to have them all resize to the same size as the quality of the picture isn't super critical. I just want the sheet to look nice and tidy.

So
-yes I want the image to resize when inserted
-I want all images the same height and width
-The placement is all ready set for each picture

Also using a chart to insert pictures does what I need and if there was a way to keep the other shapes from moving without protecting the sheet that would work as well. Not a big deal if the shapes are still able to be edited I just don't want them to move if accidentally grabbed.

 

[Yongle]

1. Place code in SHEET module - will not work anywhere else
right-click sheet tab \ click View Code \ paste code below into the window which appears \ back to excel with {ALT}{F11}

2. Click on any cell to resize all pictures

3. Amend W and H to suit your needs

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ResizePictures
End Sub

Private Sub ResizePictures()
    Const [COLOR=#ff0000]W[/COLOR] = 120
    Const [COLOR=#ff0000]H[/COLOR] = 80
    Dim i As Integer, shp As Shape
    For i = 1 To Me.Shapes.Count
        Set shp = Me.Shapes(i)
        If shp.Type = msoPicture Then
            shp.LockAspectRatio = msoFalse
            shp.Width = W
            shp.Height = H
        End If
    Next i
End Sub