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 1000 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).
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
Here’s the response on the access-programmers forum.
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.
Thx for the praise! If I find some time, I’ll have a look at the tab issue.
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.
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
Just tested Excel 2010 on XP (win32), works as expected. What errors do you get?
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
Does the downloadsample work on your system, or does it fail in a similar way? Have you tried running your form without the API calls?
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
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
Glad it works
Mooi man!
Thank you so much for publishing this, it works like a charm and took me less than 10 minutes to review and set up 2 splitters on my form.
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.
Thanks for your response. Look forward to any solution you may have.
Were you able to chase up Alain with a potential solution for the tab control in Access?
Thanks
Anything further on the Access tab control?
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