VBA image size reset vs Excel interface image size reset

[Orfil]

I use a ranged image and a scroll bar to display text pulled from a pivot table with the OFFSET function. The macro I’ve assigned to the scroll bar looks like this:

Private Sub textScroller_Change()

Application.ScreenUpdating = False
 
    Dim ws As Worksheet
    Dim shp As Shape
     
    Set ws = Sheets("Dashboard")
    Set shp = ws.Shapes("camText")
 
    Worksheets("Calc").Rows("90:95").EntireRow.AutoFit
 
    With shp
        .LockAspectRatio = msoTrue
        .ScaleHeight 1#, True, msoScaleFromTopLeft
    End With

Application.ScreenUpdating = True

End Sub

Problem is, the code that should reset the picture size each time the scroll bar scrolls doesn't work. If I manually reset the picture size, that does work. I tried changing the Scroll event to GotFocus but that doesn't work either.

Anyone with a solution or another approach, I'd be much obliged. Thank you.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Orfil]

Hi ,

See if this is OK.

Narayan

Hi, Narayan

Thanks for the reply. I wish it was that straightforward. It looks like it's working because the number of rows is smaller but then, if you keep scrolling down, row autofit wraps a big block of text and the image's aspect ratio goes out of whack (see attachment).

Here's the thing though: my VBA code works when you run it from inside the editor, but not when it's assigned to scrollbar_change.

I'd put the text table on the dashboard worksheet but then the scroll bar action produces a massive refresh flicker in the other ranged images on the worksheet each time it's clicked. I don't know which is worse, the flicker or the text distortion. At least the distortion is localised

Regards+best wishes, Orfil

 

[NARAYANK991]

Hi ,

I am unable to understand your problem.

Can you check out this attached file , and mention the date in the top left corner where you are facing a problem ?

Narayan

 

[Orfil]

Hi ,

I am unable to understand your problem.

Can you check out this attached file , and mention the date in the top left corner where you are facing a problem ?

Narayan

Hello again!

The problem will become clear to you once you've seen the fix. The overflow is obscured behind a borderless rectangular shape set to the background colour. Values are written to the worksheet for clarity.

Sub PicSize()

'   First turn off Lock Aspect Ratio & Relative to Original Picture Size
'   under Size and Properties....

    Dim ws As Worksheet
    Dim shp As Shape
    Dim RowsHeight As Single, ColumnsWidth As Single
    Dim PicHeight As Single, PicWidth As Single
   
    Set ws = Sheets("Dashboard")
    Set shp = ws.Shapes("camText")

'   Autofit the row heights to accommodate wordwrap
    Worksheets("Calc").Rows("91:94").EntireRow.AutoFit
  
    RowsHeight = Sheets("Calc").Range("A91:A94").Height
    ColumnsWidth = Sheets("Calc").Range("DS:DW").Width
    PicHeight = shp.Height
    PicWidth = shp.Width
   
    ws.Cells(2, "X").Value = RowsHeight
    ws.Cells(3, "X").Value = ColumnsWidth
    ws.Cells(4, "X").Value = PicHeight
    ws.Cells(5, "X").Value = PicWidth
   
            With shp
                 .Width = ColumnsWidth
                 .Height = RowsHeight
            End With

End Sub

 

[NARAYANK991]

Hi ,

It is good that you have fixed your problem yourself.

Narayan

 

[Orfil]

 

[Orfil]

 

[Orfil]