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

 

[Logit]

.
This code will remove the title bar creating a borderless user form. It will also allow you to move the form around the screen by click & hold the left mouse button
anywhere on the userform.

I'm not certain how you would edit the code to have only one instance of this affect multiple user forms ... so placing this in each of the userforms you want it

Thanks go to Rick Rothstein and PCMax for developing the code.

'**** Start of API Calls To Remove The UserForm's Title Bar ****
Option Explicit


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" _
                Alias "FindWindowA" _
               (ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long
  


    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
'**** End of API Calls To Remove The UserForm's Title Bar ****


'**** Start of API Calls To Allow User To Slide UserForm Around The Screen ****
    Private Declare PtrSafe Function SendMessage Lib "user32" _
                Alias "SendMessageA" _
               (ByVal hWnd As Long, _
                ByVal wMsg As Long, _
                ByVal wParam As Long, _
                lParam As Any) As Long
 
    Private Declare PtrSafe Function ReleaseCapture Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    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
'**** End of API Calls To Remove The UserForm's Title Bar ****


'**** Start of API Calls To Allow User To Slide UserForm Around The Screen ****
    Private Declare Function SendMessage Lib "user32" _
                Alias "SendMessageA" _
               (ByVal hWnd As Long, _
                ByVal wMsg As Long, _
                ByVal wParam As Long, _
                lParam As Any) As Long
 
    Private Declare Function ReleaseCapture Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Const WM_NCLBUTTONDOWN = &HA1
Private Const HTCAPTION = 2
'**** End of API Calls To Allow User To Slide UserForm Around The Screen ****


Dim hWndForm As Long


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub UserForm_Initialize()
   Dim Style As Long, Menu As Long
   hWndForm = FindWindow("ThunderDFrame", Me.Caption)
   Style = GetWindowLong(hWndForm, &HFFF0)
   Style = Style And Not &HC00000
   SetWindowLong hWndForm, &HFFF0, Style
   DrawMenuBar hWndForm
End Sub


Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
  If Button = xlPrimaryButton Then
    Call ReleaseCapture
    Call SendMessage(hWndForm, WM_NCLBUTTONDOWN, HTCAPTION, ByVal 0&)
  End If
End Sub

 

[Rufus Clupea]

Thanks, but that's exactly what I have (functionally) now.

I only have one UserForm, so I only need one iteration (so far). However, I need the code someplace else than the Userform Module itself, much as I use a short event handler for each control that then hands any processing off to another module, e.g. in the UserForm Module I'd typically have something like:

Sub OptionButtonXX_Click()
    Process_OptionButton XX
End Sub

...and then in another Module called "OB_Processing":

Sub Process_OptionButton(OB as Long)

    [Code that does whatever needs to be done with the selection of OptionButtonXX]

End Sub

I believe (not positive) I have an example of the UserForm handing off the API code to another module, but it's either very old, or missing the part that allows moving the UserForm, etc. I'll try to find it and amend to this thread in case someone might be able to help me adapt it...

 

[Logit]

.
Other than a personal desire to have the least amount of coding within the UserForm itself .... I don't understand the reluctance
of having it there ?

Have you experienced some conflict between the borderless macro code and the other macros ?

 

[Rufus Clupea]

Other than a personal desire to have the least amount of coding within the UserForm itself .... I don't understand the reluctance
of having it there ?

I thought I mentioned that in my OP... Yup, I did...

I need to save as much space as possible in the UserForm Module due to a large number of Controls on the UserForm.

I guess I could/should have been clearer.

There are/will be several hundred Controls on the form (800+ at last count, and that could go up); each requiring an event handler in the UserForm Module. Even with a combined/"universal" event handling Sub (not yet implemented/tested), I still expect a need to conserve code/space in that Module.

I wrote:

I believe (not positive) I have an example of the UserForm handing off the API code to another module...

I found that code. In the example:

'PLACE IN YOUR USERFORM
Private Sub UserForm_Initialize()
    HideTitleBar Me
End Sub

...and...

'PLACE IN A STANDARD MODULE
'CODE FROM https://wellsr.com/vba/2017/excel/remove-window-border-title-bar-around-userform-vba/
Option Explicit
Option Private Module

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Public Declare PtrSafe Function GetWindowLong _
                           Lib "user32" Alias "GetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long) As Long
    Public Declare PtrSafe Function SetWindowLong _
                           Lib "user32" Alias "SetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long, _
                           ByVal dwNewLong As Long) As Long
    Public Declare PtrSafe Function DrawMenuBar _
                           Lib "user32" ( _
                           ByVal hWnd As Long) As Long
    Public 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] 
    Public Declare Function GetWindowLong _
                           Lib "user32" Alias "GetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long) As Long
    Public Declare Function SetWindowLong _
                           Lib "user32" Alias "SetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long, _
                           ByVal dwNewLong As Long) As Long
    Public Declare Function DrawMenuBar _
                           Lib "user32" ( _
                           ByVal hWnd As Long) As Long
    Public 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
Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

The problem with this code is that (as written) it doesn't/won't allow moving the UserForm around. My attempts to kluge that additional code onto the Sub have thus far failed (I'm still quite new/inexperienced with VBA).

 

[Rufus Clupea]

Other than a personal desire to have the least amount of coding within the UserForm itself .... I don't understand the reluctance
of having it there ?

I thought I mentioned that in my OP... Yup, I did...

I need to save as much space as possible in the UserForm Module due to a large number of Controls on the UserForm.

I guess I could/should have been clearer.

There are/will be several hundred Controls on the form (800+ at last count, and that could go up); each requiring an event handler in the UserForm Module. Even with a combined/"universal" event handling Sub (not yet implemented/tested), I still expect a need (not personal desire ) to conserve code/space in that Module.

I wrote:

I believe (not positive) I have an example of the UserForm handing off the API code to another module...

I found that code. In the example:

'PLACE IN YOUR USERFORM
Private Sub UserForm_Initialize()
    HideTitleBar Me
End Sub

...and...

'PLACE IN A STANDARD MODULE
'CODE FROM https://wellsr.com/vba/2017/excel/remove-window-border-title-bar-around-userform-vba/
Option Explicit
Option Private Module

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Public Declare PtrSafe Function GetWindowLong _
                           Lib "user32" Alias "GetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long) As Long
    Public Declare PtrSafe Function SetWindowLong _
                           Lib "user32" Alias "SetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long, _
                           ByVal dwNewLong As Long) As Long
    Public Declare PtrSafe Function DrawMenuBar _
                           Lib "user32" ( _
                           ByVal hWnd As Long) As Long
    Public 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] 
    Public Declare Function GetWindowLong _
                           Lib "user32" Alias "GetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long) As Long
    Public Declare Function SetWindowLong _
                           Lib "user32" Alias "SetWindowLongA" ( _
                           ByVal hWnd As Long, _
                           ByVal nIndex As Long, _
                           ByVal dwNewLong As Long) As Long
    Public Declare Function DrawMenuBar _
                           Lib "user32" ( _
                           ByVal hWnd As Long) As Long
    Public 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
Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

The problem with this code is that (as written) it doesn't/won't allow moving the UserForm around. My attempts to kluge that additional code onto the Sub have thus far failed (I'm still quite new/inexperienced with VBA).

 

[Logit]

.
You can download a copy of the working project here : https://www.amazon.com/clouddrive/share/4iwz8UUqlgl4e3ZErmcx5plI2AeOxFNidx7mSxdE829

I would caution you regarding the use of so many controls on your userform. Think in terms of the workbook / form being 'user friendly'. The design you
have envisioned may technically work from a coding/programming view but will it be productive in terms of the user ?


Technically this works :

But this is more efficient and user friendly :

Perhaps you could break down the single userform into several different userforms ? Separate the various functions in to groups ... placing each group onto it's own form ?

 

[Rufus Clupea]

Perhaps you could break down the single userform into several different userforms ? Separate the various functions in to groups ... placing each group onto it's own form ?

Been there–done that.

At first I thought of using several UserForms, but there still would have been a couple dozen. The MultiPage Control is a MUCH better way of organizing things (IMO). Most of the UserForm is now occupied/organized by a 20 tab MultiPage.

At first I was using just the UserForm Module for most of the processing, but I breached the 75k character module limit at about a third of the way through the project. I realized then that I had to break it up into several modules. Now the UserForm Module just contains brief event handling Subs that hand the work off to other modules.

Here's an example of a stripped-down ("lite") version of what I'm doing (except that I'm using Excel to do it). My version won't be stripped-down; it'll include many more advantages, disadvantages, & skills.

Any thoughts on how to adapt/add the code to move the UserForm via mouse?

 

[Logit]

Any thoughts on how to adapt/add the code to move the UserForm via mouse?

Sorry ... I do not.

Perhaps someone else on the FORUM can assist ?

 

[Rufus Clupea]

Let's hope so...

Since my last post, I have (finally) been able to get the UserForm to move by copy/pasting the UserForm_MouseDown and UserForm_MouseMove Subs (and the FormX, FormY variables) from Post #1 into the UserForm Module of Post #5 . This gets me most of the way there.

After some reading, it became somewhat clearer that UserForm_MouseDown and UserForm_MouseMove are event handlers, so—my understanding is that—their declarations must be in the UserForm Module. My last question still remains, how can/do I keep the event handler "envelope" (3 lines of code) in the UserForm Module, but move the "processing" to another standard module, i.e Module1?

I realize this may sound picky, (it only saves me 10 lines of code in the UserForm Module) but if anyone has read this thread, hopefully they'll understand my reasons (hundreds of Controls) for keeping the UserForm code to an absolute minimum.

Again, TIA