Power Tip: Scale Access Chart objects at runtime

Today’s guest blogger is Peter De Baets of Peter’s Software, providing Microsoft Access tools to developers for over a decade including ShrinkerStretcher,  KeyedAccess and many other products and free downloads.

I've recently discovered an easy way to scale Access Chart objects at runtime to fit the form window, and I'd like to share it with you. Scaling an Access Chart means that when the end-user resizes the chart form window, the chart resizes to fit the new window size. It also means that when the Access window and form are maximized, the chart will fill the screen at any screen resolution. Well, it will mostly fill the screen, as we'll see below. Anyway, scaling an Access Chart is a no-brainer - it's a great way to add some WOW to your application with very little effort.

(Before)

(After)

This tip is for forms that contain one and only one chart control (Unbound Object Frame containing a Microsoft Graph Chart object), and are intended simply to display that one chart.

I thought this would be fairly easy - you know, just set the chart control Height and Width to the form InsideHeight and InsideWidth property values in the form OnResize event procedure - but it turns out that there is a hitch. The chart object scales smoothly as the chart control increases in size until the width exceeds about 9.7 inches. Then the chart object undergoes an anomalous growth spurt and part of the chart object resizes beyond the edge of the chart control hiding some of the chart data! Come back, my chart!

The only way to handle this, as far as I know, is to limit the width of the chart control to a maximum of 9.7 inches so all of the chart data remains visible. Limiting the width of the chart control means there may be some empty form space on the right, so the code below also centers the chart control on the screen to make up for this.

Without further ado,  here is the code to place in your chart form OnResize event procedure. Remember to insert the name of your chart control where indicated:

Dim wd As Long
With Me!OLEUnbound0 '<-- Your chart control name goes here
    '* Make sure that the chart control size 
    '* mode is set to 'stretch'
    .SizeMode = acOLESizeStretch
    
    '* Make sure that chart fonts scale 
    '* with the chart object
    .Object.ChartArea.AutoScaleFont = True
    
    '* If window resizing causes horizontal 
    '* scroll bar to appear, 
    '* then move chart control out of the way prior to 
    '* setting left, width values.
    If Me.Width > Me.InsideWidth Then
        Application.Echo False
        .Left = 0
        .Width = 0
    End If
    
    '* Set the new width of the chart control.
    '* Chart object can resize beyond the chart 
    '* control when control is wider than ~9.7 inches, so
    '* limit chart control width to 9.7 inches max.
    wd = Me.InsideWidth
    If wd > 9.7 * 1440 Then
        wd = 9.7 * 1440
    End If
    
    '* Set chart control width and height
    .Width = wd
    .Height = Me.InsideHeight - .Top
    
    '* Center the chart horizontally on the form
    If Me.InsideWidth > .Width Then
        .Left = (Me.InsideWidth - .Width) / 2
    End If
    
    '* Set the form width and height
    Me.Width = .Left + .Width
    Me.Section(acDetail).Height = Me.InsideHeight
    
    Application.Echo True

End With