Titleless UserForm: Code in wrong(?) Modules...

[Rufus Clupea]

Hi Folks,

I've been trying to adapt some code from a VBA book, but I can't seem to get it right (most likely because it's all Ελληνικά to me... ). The code is for a title-less UserForm. Examples are abundant on the Web, but I haven't (yet) found one that does what I'd like to do, the way I'd like to do it. Also, I'm using Excel 2016, and many/most of the examples I've found are >= 10 years old.

The example I have has this in a "standard" Module:

Option Explicit

Sub ShowUserForm()
    UserForm1.Show
End Sub

Sub ShowUserForm2()
    UserForm2.Show
End Sub

...and this in two different UserForm Modules:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 And Win64 Then
    Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
    Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    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 Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Const GWL_STYLE = -16
Const WS_CAPTION = &HC00000


'UserForm position
Dim FormX As Double, FormY As Double

Private Sub UserForm_Initialize()
    Dim lngWindow As Long, lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, Me.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'   Starting position when button is pressed
    If Button = 1 Then
        FormX = X
        FormY = Y
    End If
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'   Move the form if the left button is pressed
    If Button = 1 Then
        Me.Left = Me.Left + (X - FormX)
        Me.Top = Me.Top + (Y - FormY)
    End If
End Sub

Private Sub CloseButton_Click()
    Unload Me
End Sub

It seemed odd (and inefficient) to me to duplicate this code in 2 UserForm Modules.

What I'd like to do is have the code that modifies the UserForm (and the code that allows a user to move the UserForm around on the screen) in a single Module, and have the event handler for closing the form in the UserForm Module.

Does that make sense? (I need to save as much space as possible in the UserForm Module due to a large number of Controls on the UserForm.)

TIA

 

[My Aswer Is This]

You said in one of your earlier post:

I'm still quite new/inexperienced with VBA)

Maybe if you explained what your attempting to do with all these controls or provided some insight what your project is about we could provide ways to eliminate some of these controls.

Wow having hundreds of controls on a Userform and writing all the code for all these controls for a person within limited Vba experience this surely must be a challenge.

I have used Multipages and used Scroll Bars on my Multipages and been able to put hundreds of controls on just one Multipage if needed. But then I have never attempted to make a Userform with hundreds of controls.

And eliminating the Title bar on my Userform would only save me maybe 1/8th of a inch.
​

 

[Rufus Clupea]

Maybe if you explained what your attempting to do with all these controls or provided some insight what your project is about we could provide ways to eliminate some of these controls.​

I have; it's the same project you helped me with in a previous thread (or 2).
Post #8 of this thread should give anyone a fair idea of what I'm doing.
There's really no way to eliminate any beyond the dozen or so I've figured out ways to combine.

Wow having hundreds of controls on a Userform and writing all the code for all these controls for a person within limited Vba experience this surely must be a challenge.

I have used Multipages and used Scroll Bars on my Multipages and been able to put hundreds of controls on just one Multipage if needed. But then I have never attempted to make a Userform with hundreds of controls.

And eliminating the Title bar on my Userform would only save me maybe 1/8th of a inch.

​

Again, Post #8 explains that I'm using a MultiPage on the UserForm to organize the controls.

A challenge? Fer Sure, but an opportunity to combine 2 hobbies into one project. It's a challenge I've taken on before, doing a similar (albeit much smaller) project in the early '80s on a Commodore 64 (my first exposure to computers & programming).

Writing code for so many controls can get tedious, yet no 2 are exactly the same.

I also learn faster/better by doing—working on a project I can relate to.

Eliminating the Title Bar gets me 2 lines worth of text output, but it also looks much cleaner, and gives me a bit more room for a custom graphic or 2...

 

[Logit]

but move the "processing" to another standard module, i.e Module1?

I don't believe Excel / VBA will let you do that.

Does anyone else know of a way ?

 

[My Aswer Is This]

Well thanks for that information. Hope your project works out. I always use my UserForm Title Bar for a place to post comments I miight want to pass along as my script runs.

I have; it's the same project you helped me with in a previous thread (or 2).
Post #8 of this thread should give anyone a fair idea of what I'm doing.
There's really no way to eliminate any beyond the dozen or so I've figured out ways to combine.


​

Again, Post #8 explains that I'm using a MultiPage on the UserForm to organize the controls.

A challenge? Fer Sure, but an opportunity to combine 2 hobbies into one project. It's a challenge I've taken on before, doing a similar (albeit much smaller) project in the early '80s on a Commodore 64 (my first exposure to computers & programming).

Writing code for so many controls can get tedious, yet no 2 are exactly the same.

I also learn faster/better by doing—working on a project I can relate to.

Eliminating the Title Bar gets me 2 lines worth of text output, but it also looks much cleaner, and gives me a bit more room for a custom graphic or 2...

 

[Logit]

.
After some pondering .... have you considered not using a UserForm ?

Why not place all of your controls on a single sheet (or several different sheets) and go from there ?

I think you can have a single macro the size of 64 KB. That is a very large macro !

 

[Rufus Clupea]

I always use my UserForm Title Bar for a place to post comments I miight want to pass along as my script runs.

I did similar at first, displaying the dimensions, offsets, and other general info about the form to make it easier to reproduce if necessary. The main UserForm is also "Full Screen" except for the bottom task bar. I think this also makes for a "cleaner" look for uninitiated users—without distractions around the borders/background. Eventually, I'd like to make it (the form) dynamic in the sense that it'll (hopefully) look the same on other computers/screens (but that's a whole different ball-o-worms for another time)...

Similar to how you do things (if I'm reading/understanding you correctly) I have data around the MultiPage that updates as choices are made on the MultiPage—stuff a user would want to see at all times as their choices affect changes.

 

[Rufus Clupea]

.
After some pondering .... have you considered not using a UserForm ?

Why not place all of your controls on a single sheet (or several different sheets) and go from there ?

I think you can have a single macro the size of 64 KB. That is a very large macro !

Valid questions.

Yes, I did consider those things. In fact, I tried to do it directly on the Spreadsheet the first time through. I ran an early version (what I had of one, anyway) past a potential user. This person had no experience with/exposure to Spreadsheets, and found the whole thing quite confusing/frustrating. There were also quite a few organizational problems that the UserForm & Multipage solve (one being that the user doesn't have to scroll around and/or get lost).

Yes, 64K characters is the module limit (I mistakenly stated it as 75K in an earlier post). As I mentioned, I hit that mark at about 25%-33% of the way through the project, and realized I had to split things up (which created its own problems).

Things are going MUCH better this way; I just have to keep aware of the size of the UserForm module. With a few hundred controls, SpinButtons have become SBs, CheckBoxes CBs, and OptionButtons OBs. Lots of commenting, but that doesn't seem to count against the 64K limit.

 

[Logit]

.
Best wishes on your project.

 

[Rufus Clupea]

Thank you. It's actually coming along quite well. When I get stuck on something, I come here & ask, and someone can usually point me in the right direction, for which I'm appreciative!

 

[My Aswer Is This]

One thing I like about Userforms is that you can install them in your Personal Folder and then have them available on any Workbook you open.

I use mine to launch Macro's I want to use on any workbook. I also use them to link me to Web sites and a host of things. Keeps me from having to remember a dozen different shortcut keys. And with using Multipages I can sort my controls into categories. One Multipage has all my Web page links. One has all my Address books or my what ever.

I added scrollbars to my Multipages so my Userform can be small but I can have a lot of controls on a small UserForm. I can have the Userform displayed on my screen but since it's small and opened Modeless I can still see my worksheet and work on it with my Userform displayed.

I use a Listbox with all the Active workbook sheet names and by just clicking on the sheet name it will take me to that sheet. A easy way to get to a sheet if you have numerous sheets in your Workbook.

I know some People do not like UserForms but I love them. I wonder sometimes maybe those who hate them just have never looked into them enough to know all they can do.

I later on learned you can make them very colorful also with images and colored controls.

Glad to see your project is coming along.

But again I have never tried using 800+ controls.
My projects have never been that big.

One thing I like to do is use the call feature.

I can write one script name GoHome for example.
And have this script in My Userform

And then just put "GoHome" literally that's all not the whole script in a dozen different controls and be able to run that script. No need writing the same script a dozen times.

Then also if I want to modify that script I only have to modify the one script.