CODEWITHSUNDEEP
vbaexcel
Steven Martin
Steven Martin

Reputation: 3272

Excel Camera Tool - Resize image to 100% scale

I am trying use an image to display different tables of data using the camera tool.

As a test the below code works,

  1. i change the shape formula to a new range, and then i update the shape scale as it doesn't automatically update when the data is on a different sheet

    2.This only works if i click the button twice. 1st click changes the source, but i have to click it a second time to get the scale to reset to 100%

Any ideas why this is?

Private Sub CommandButton1_Click()

Shapes("Display").DrawingObject.Formula = "=Pivots!AS2:BB10"
Shapes("Display").DrawingObject.ShapeRange.ScaleWidth 1, msoTrue
Shapes("Display").DrawingObject.ShapeRange.ScaleHeight 1, msoTrue

End Sub

Private Sub CommandButton2_Click()

Shapes("Display").DrawingObject.Formula = "=Pivots!AS13:BA27"
Shapes("Display").DrawingObject.ShapeRange.ScaleWidth 1, msoTrue
Shapes("Display").DrawingObject.ShapeRange.ScaleHeight 1, msoTrue

End Sub

Upvotes: 1

Views: 773

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

DoEvents is like a magical incantation that I try when things like this happen. It usually doesn't help, but this time it did! 

Private Sub CommandButton1_Click()

Shapes("Display").DrawingObject.Formula = "=Pivots!AS2:BB10"
DoEvents
Shapes("Display").DrawingObject.ShapeRange.ScaleWidth 1, msoTrue
Shapes("Display").DrawingObject.ShapeRange.ScaleHeight 1, msoTrue
End Sub

Private Sub CommandButton2_Click()

Shapes("Display").DrawingObject.Formula = "=Pivots!AS13:BA27"
DoEvents
Shapes("Display").DrawingObject.ShapeRange.ScaleWidth 1, msoTrue
Shapes("Display").DrawingObject.ShapeRange.ScaleHeight 1, msoTrue
End Sub

Upvotes: 3

Related Questions