VBA splitter control to dynamically resize controls on a userform

Here’s another post of something I created long ago, yet never got to post. Just trying to catch up here 🙂

While looking for a control myself to achieve this, I couldn’t find any. Some VB6 controls we’re available, yet when distributing spreadsheets or databases I just don’t like to create to much dependencies on external controls. Quite often the support required goes through the roof when external dependencies are used.

Finally I found something that I could have used, yet it didn’t suit my needs well enough, so I decided to rewrite the whole thing, but retaining the concept. The original was created by Dev Ashish and Marc Chouteau and can be found on the Access MVP site. It was build on Access and didn’t work on any of the other Office applications, additionally it didn’t support nesting multiple splitters and controls.

Userform with 4 vertical and 1 horizontal splitter bars

As mentioned my code uses the same concept still, a label control on the location of the splitter, then the events of the label can be used to track any splitter activity to be executed. It supports both Access and Excel (and other Office apps), but it requires a compiler directive to be set (Access has different form properties than the other apps). The main code is contained in two classes, a splitter and a helper class. There is an aditional module that is used for some support functionality (this one is slightly different for the Access version). The whole thing is VBA only, so no external dependencies are required.

The code can be downloaded in a zip file (downloaded 3138 times). It has 2 examples, one for Excel and one for Access (the Excel example has detailed comments in its code on how to set things up, especially with nested splitters).

UPDATE (26-aug-2013)

The Access version will not work on tab controls (but that can probably be overcome easily) and not on resizeable forms (very hard, due to the order of events Access generates and the modifications that are automatically made)

Alain Darscotte kindly provided me with his updated Access version of this splitter that (supposedly, haven’t checked) handles resizing and/or tab controls better.

It has extensive modifications, I haven’t tried it and I only provide it here as a download. (downloaded 1414 times). The download contains the email exchange with Alain, with some comments by him. Apologies for the long time it took me to actually post it here.

[ad name=”468×60 Banner”]

Be Sociable, Share!

31 thoughts on “VBA splitter control to dynamically resize controls on a userform

  1. Hi. I have tried to run the ACCESS 2007 example. It looks for an ActivX which i have failed to find which one. Can you please help me with this ?
    Thanks,
    JD

  2. This is great code and thanks for publishing. One note to the readers: this code will need to be modified if you plan on using the splitter inside of a tab control. I’m very impressed.

  3. Hello Dudes!

    I’m trying to download the current example but I did not succeed… is there any other way I can get it? Through email perhaps!

    I’m having an issue with a VBA application and I’d like to explore your solution and see if it fits to my needs.

    Best regards and I’d appreciate your comments.

  4. Hi,
    I just wished to thank you very much for offering such a fantastic tool. It is really impressive.
    May I just ask you why I can resize left & right windows in following envirt : Win7 (64) & Excel 2010, whereas it is not possible to resize under : Win XP, Excel 2007 / 2010 ? How could I fix this, please ?
    Anyway, it’s just a great tool !
    Thanks again to all the authors,
    Fxr

  5. Hi,
    Thanks for your interest & quick response.
    I don’t get any error msg. When mousing over the Splitter label, I just don’t get the MousePointer & therefore cannot resize (An Excel-Type cross : + is displayed instead).
    Tested on 2 different PCs Excel 2007 & 2010, both running under XP…
    Extra info that may cause the PB (Only under XP) :
    I also make the userform modeless & transparent by using the following Code :

    Option Explicit
    ‘ for testing drop a commandbutton on the form
    ‘ named CommandButton1, so you can close the form!

    Private Declare Function FindWindow Lib “user32” _
    Alias “FindWindowA” ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Declare Function GetWindowLong Lib “user32” _
    Alias “GetWindowLongA” ( _
    ByVal hwnd As Long, _
    ByVal nIndex As Long) As Long

    Private Declare Function SetWindowLong Lib “user32” _
    Alias “SetWindowLongA” (ByVal hwnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

    Private Declare Function DrawMenuBar Lib “user32” ( _
    ByVal hwnd As Long) As Long

    Private Const GWL_STYLE As Long = -16
    Private Const WS_CAPTION As Long = &HC00000

    Private Const GWL_EXSTYLE As Long = -20
    Private Const WS_EX_APPWINDOW As Long = &H40000

    Private Sub UserForm_Initialize()

    ‘Initialisation Splitter
    ‘=======================
    Set Split1 = New clsSplitter
    With Split1
    .AddLeftControl Me.TreeView1, 0, 1, 20 ‘ move factor 0% (on left: distance left-side-of-form to splitter remains same), size factor 100%
    .AddRightControl Me.WebBrowser1, 0, 1, 20 ‘ move factor 0% (on right: distance splitter to textbox remains same), size factor 100%
    .AddRightControl Me.ListView1, 0, 1, 20 ‘ move factor 0% (on right: distance splitter to textbox remains same), size factor 100%
    Call .Init(Me.Label_Splitter, 10) ‘ Initialize, use Label3 as splitter, minimum border between controls and form = 10
    End With

    ‘ create setup string for restore actions
    strSetup = Split1.State

    Remove_Borders_And_Caption_Bar Me.Caption

    formhandle = FindWindow(vbNullString, Me.Caption)
    SetWindowLong formhandle, GWL_EXSTYLE, GetWindowLong(formhandle, GWL_EXSTYLE) Or WS_EX_LAYERED
    SetLayeredWindowAttributes formhandle, vbCyan, 0&, LWA_COLORKEY
    Me.BackColor = vbCyan

    End Sub

    Sub Remove_Borders_And_Caption_Bar(StCaption As String)

    Dim lStyle As Long
    Dim hMenu As Long
    Dim mhWndForm As Long

    If Val(Application.Version) < 9 Then
    mhWndForm = FindWindow("ThunderXFrame", StCaption) 'XL97
    Else
    mhWndForm = FindWindow("ThunderDFrame", StCaption) 'XL2000+
    End If

    lStyle = GetWindowLong(mhWndForm, GWL_STYLE)
    lStyle = lStyle And Not &HC00000

    SetWindowLong mhWndForm, GWL_STYLE, lStyle

    SetWindowLong mhWndForm, GWL_EXSTYLE, WS_EX_APPWINDOW

    DrawMenuBar mhWndForm

    End Sub

    Thanks again for your help.
    Best regards,
    Fxr

  6. Very good point.
    I’m so atached to my ultimate goal that I didn’t check.
    Will try it tomorrrow & let you know.
    Thanks & Regards,
    Fxr

  7. The downloadSample works fine.
    I just had to take Userform Transparency away & it seems OK now.
    Sorry to have requested some of your time on this.
    Thanks,
    fxr

  8. Great splitter. I’m currently using it in MS Access 2010 but am having problems when I have a tab and a subform in the tab. If I move it slowly up and down all works well but if I move it quickly down the bottom tab moves down the page and goes off the page. I think it is something to do with havig to resize both the sub form and the tab at the same time. Any ideas on what could be causing this?

    Thanks

    • That’s a know issue, the Access tab-control is a nasty one. Alain Darscotte ran into this as well and I think he managed to find a solution which I haven’t seen yet. Will see whether I can post an update here.

  9. Hi Thijs,
    About the problem with Tabs in the splitter, now I have some time to send an answer, but I think it’s better that I send directly the code of the solution in a MS Access ACCDB file . The problem is that I don’t know how I can add the ZIP file in your web site.
    Then I think it’s better that I send the ZIP file to you and then you can add it in your web site

    Best regards

  10. Hi,
    thanks for this post. The code is working great for me.
    May I suggest to change the splitter control from label to image? This would add an easy way of presenting sth. like a grab handle as we see it in a lot of applications. I tried changing the control type and it worked fine. Just had to add a small gif to the splitter which is then presented in the middle of the image-control.
    Have tested in Access 2007 only.

    Thanks again, and best regards

    tom

  11. Great splitter when run in Excel 2003 (XP2003) but when I get the 2 objects and the recreate the example form in Access 2003 (XP2003) i get overflow errors.
    I ‘ve set #Const ACCESS = True
    Perhaps the way access forms measure heights and widths, is different in an Excel form.
    As you can see, i’m stuck. But this is the tool i want to use. I don’t want anything else. So, i was wondering if you could review it in Access2003 on XP.

    TIA
    Iordanis

  12. how can i set the splitter control up, so it also works in a resizeable access form? When the form opens as Normal, the splitter is OK with the form’s bounds. When i maximize the form, the splitter stops dragging, at the same spot as before. It doesn’t take into account the new width/height of the form.

    TIA
    Iordanis

    • This is nasty Access behaviour, which is very hard to bypass. I once looked into it but it seems that the event order that is being generated is weird/quirky, which causes this behaviour.

      I did receive some samples from Kym, he managed to get some stuff to work, but haven’t been able to post this code yet.
      Thijs

  13. Again, what a wonderful control!

    But I can’t seem to produce the following scenario:

    Say, create an access resizable form, that has 3 horizontal text boxes, separated by 2 horizontal splitters which are controlled independently of each other (I mean, not all controls are resized by the movement of any splitter, but only those which are separated by it)

    That is:
    textbox1 and textbox2 are just resized by the 1st horizontal splitter which separates them and the position of the 2nd splitter and size of textbox3 are unchanged.

    Equally, textbox2 and textbox3 are just resized by the 2nd horizontal splitter which separates them and the position of the 1st splitter and size of textbox1 are unchanged.

    I can’t manage to set the parameters right. Especially when the form is resized or maximized, without the size of the controls being altered strangely.

    I hope i made my point understood.
    THXS
    Iordanis, Greece

    • The control cannot do what you want. Maybe it can be made to do so, but currently it can’t. Here’s why; every splitter can have nested splitters/controls. But that is a strict relationship, a splitter and all of its controls can be nested within another splitter. Now in your case you have textbox2, which is part of both splitter1 and splitter2. To make it work correctly, you would need to place textbox2, textbox3 and splitter2 (that is; all of splitter2) inside splitter1.

      But then again; that is not what you want to achieve. Sorry…

Leave a Reply to RoderickE Cancel reply

Your email address will not be published. Required fields are marked *

 

Subscribe without commenting