Search code examples
excelvbaclassscrollbaruserform

Scrollbar height in userform frame doesn't update right away. I need to click it to update it

So I have an Excel userform that contains a frame. In this frame, there may be a X amount of dynamically added objects that fill it's content until it overfills. Of course I added a vertical scrollbar to compensate this issue, this is not the problem.

The bottom frame with caption "Données" is the one im talking about: The bottom frame with caption "Données" is the one I'm talking about

The problem is much trickier. So as you can see in the previous image, there are a bunch of buttons (actually toggle buttons). When you toggle a set of each of them, they will appear in the frame below. In fact, all the possible combinations of the toggled buttons will be shown.

Example 1: Example 1

Example 2: Example 2

The toggle buttons click methods are actually programmed in a class-module that calls a sub directly in the userform (named RSAjout2).

Option Explicit

'THIS PIECE OF CODE IS WRITTEN IN A CLASS-MODULE

' Is not useful for this demonstration
Public WithEvents btn As MSForms.ToggleButton
Public frm As UserForm
'''


' Here, each button is an object that calls the refreshData function to make sure it is updated each
' time one of then is pressed

Private Sub btn_Click()
    'We call the subprocedure in the userform RSAjout2
    RSAjout2.refreshData
End Sub

When this sub (refresh data) is called, there is a part of its code that ajusts the height of the scrollbar. 

Public Sub refreshData()

'###Lots of code that I dont have any problem with###

    RSAjout2.CadreDonnees.ScrollHeight = totalHeight
    'Where totalHeight is just the height of all the frames stacked in the big frame


End Sub

And that works fine too. (So what is the problem then?)

The problem is that when the code actually resizes the height of the scrollbar, it doesn't actually update! I have to either click on the scrollbar itself (and move it) to make it change shape or click another toggleButton (but its state is an old one). Let me show you with pictures.

If I make a combination of tests and medias so that the number of frames overfill the container frame, it should display a scrollbar right (well its height should update)?

Wrong: Wrong

Then if I click the scrollbar, and move it just as it already existed, it suddently updates it is now visible.

Why? Why?

This only happends with my toggleButtons. If I, for example, use my "Abondonner" button to change the height of the scrollbar, it works perfectly. Why is that so?

Private Sub Abandonner_Click()
    'Code to test if the scrollbar updates normally from a normal click procedure
    Me.CadreDonnees.ScrollHeight = 1000
    'Spoiler* It works perfectly
End Sub

EDIT: This seemed to do the trick. However, it is clearly not the best way to solve this problem. Well at least it works...

    'An aweful, but working solution
    CadreDonnees.ScrollTop = 1
    CadreDonnees.ScrollTop = 0
    CadreDonnees.ScrollHeight = totalHeight
    CadreDonnees.ScrollTop = 1
    CadreDonnees.ScrollTop = 0
Solution

  • Try invoking the Repaint method of your Frame control =)

    CadreDonnees.Repaint