Changing Chart Axes via scrollbar

[ErikJan]

I have a scrollbar that I'd like to control X-axis min & max values in a chart (actually two at the same time).

I'm using something like this:

Code: Select all

Private Sub ScBar_Change()
    Dim v As Integer
    v = ActiveSheet.ScBar.Value
     
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlCategory).MinimumScale = v
        ActiveChart.Axes(xlCategory).MaximumScale = v + 100
        ActiveSheet.ChartObjects("Chart 4").Activate
        ActiveChart.Axes(xlCategory).MinimumScale = v
        ActiveChart.Axes(xlCategory).MaximumScale = v + 100

It works but the chart (=axes) is only updated when I release the slider, it does not 'scroll' when I slide. However if the code is only this:

Code: Select all

 Debug.Print "Scroll:" & ActiveSheet.ScBar.Value

The in the debug window I get continuous updated while I'm dragging... why is this not happening for my charts?

[HansV]

Are you sure? Both the chart axis and the value in the Immediate window are updated continuously if I click and hold the up arrow at the top or the down arrow at the bottom, or the space between the slider and those arrows, but neither is updated while I drag the slider - only when I release the slider do both get updated.

[ErikJan]

That's odd... when I slide I see continuous updates. I just created a new sheet and build this from scratch and now I see what you see as well: only updates when I release the slider... I'm investigating to see what makes this happen...

Update... I added the file with all deleted except the scrollbar (and one line of code). Look in the VBA immediate window when you drag the slide...

[ErikJan]

Hans, could you reproduce?

[HansV]

You had apparently added the workbook after I had read your reply. It's better to add a new reply than to add information to a new reply.

The difference is that in your original example, you're using the Change event of the scrollbar. In the attachment, you use the Scroll event.

[ErikJan]

You are right... result of my efforts to see what the cause was.. sorry for that. It is not about the change event however. If I do this:

Code: Select all

Private Sub ScBar_Scroll()
    Debug.Print "Scroll:" & ActiveSheet.ScBar.Value
End Sub

I get continuous updates when I drag the slider. However if I add a few lines (to try and do what I really want), it doesn't work anymore and I can't drag

Code: Select all

Private Sub ScBar_Scroll()
    Debug.Print "Scroll:" & ActiveSheet.ScBar.Value
    Dim v As Integer
    v = ActiveSheet.ScBar.Value
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlCategory).MinimumScale = v
    ActiveChart.Axes(xlCategory).MaximumScale = v + 100
End Sub

[HansV]

That's because you activate the chart, thereby taking focus away from the scroll bar.
There are few situations in which selecting cells or objects in VBA code is useful. As a rule, you should try to avoid it.
Code runs faster, more efficiently and with fewer side effects if you don't select things.

Code: Select all

Private Sub ScBar_Scroll()
    Debug.Print "Scroll:" & ActiveSheet.ScBar.Value
    Dim v As Integer
    v = ActiveSheet.ScBar.Value
    With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory)
        .MinimumScale = v
        .MaximumScale = v + 100
    End With
End Sub

Movie.zip

[ErikJan]

Yes, works! Thank you (what tool did you use to make the little movie? I like it!)

[HansV]

(what tool did you use to make the little movie? I like it!)

I used the paid version of FastStone Capture.