Hide userform title bar, and locking the userform into a relative position on the application.

[superstan2310]

Hi All,

Currently working on a project and I'm trying to get a button located in the bottom right of the application window at all times, and staying there, even if the sheet is scrolled up/down/left/right or if the application window is moved.

I have created a userform (which just contains a command button) and added the below code into it as of this moment, and functionally it is everything I need, except for that fact that the button appears with a title bar, a close button, and can be moved by the user when dragging the title bar. Qualities that I do not wish it to have.

I have seen videos and forum threads about hiding the title bar (and therefore the close button, and prevents people moving it), which clearly shows it is possible, however when I copy the code they use and incorporate it into mine one of three things happen:
1. It outright doesn't do anything.
2. It creates a white box (the size of the userform) in the middle of the screen that just blocks the view of anything beneath it.
3. It stops my code below from working.

There is a bit of code left out of the below, but that is just code in the "ThisWorkbook" section for creating a modeless userform on workbook open, and calling the SetPos macro when the window is resized.

Would anyone be able to help with keeping the functionality of the below code, while being able to remove the title bar? If the code has to be rewritten from scratch, I don't mind.

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hWnd As LongPtr) As Long
    Private Declare PtrSafe Function SetParent Lib "user32" (ByVal hWndChild As LongPtr, ByVal hWndNewParent As LongPtr) As LongPtr
    Private hWndForm As LongPtr, WbHwnd As LongPtr
    
#Else
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hwnd As Long) As Long
    Private Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long
    Private hWndForm As Long, WbHwnd As Long
#End If


Private Sub UserForm_Activate()
    WbHwnd = Application.hWnd
    Call IUnknown_GetWindow(Me, hWndForm)
    Call SetParent(hWndForm, WbHwnd)
    Call SetPos
End Sub

Public Sub SetPos()
    Me.StartUpPosition = 0
    Me.Left = Application.Width - 150 - Application.Left
    Me.Top = Application.Height - 125
End Sub

 

[Dan_W]

@superstan2310
I am not sure I fully understand. It seems to me that you are adding the commandbutton @ design time while my code adds the commandbutton for you at runtime. You just need to specify the button location, size etc in the parameters of the AddFloatingButton and it should do all the work for you.

Did you download the file demo I provided in post#6 ? And if so, did it work for you as shown in the animated gif in post#6 ?

Edit:
@Dan_W
Did you test the code in post#6 ? Did it work as advertised?

I did try it, and it worked perfectly. I tried resizing, maximising, minimising - all fine. I added a new worksheet, and one of the four buttons (the first one) appeared on the new sheet, which I have just checked the code and can see that this was intended behaviour. Excellent.

I saw your Unload_Macro routine - did you pass the Userform as a pointer because it's just not possible to pass it as an object via the commandbars OnAction / Parameter properties (I tried both methods, couldn't get it to work, which makes sense)?

 

[superstan2310]

@superstan2310
I am not sure I fully understand. It seems to me that you are adding the commandbutton @ design time while my code adds the commandbutton for you at runtime. You just need to specify the button location, size etc in the parameters of the AddFloatingButton and it should do all the work for you.

Did you download the file demo I provided in post#6 ? And if so, did it work for you as shown in the animated gif in post#6 ?

Edit:
@Dan_W
Did you test the code in post#6 ? Did it work as advertised?

Hi Jaafar, the reason I am not just using the AddFloatingButton code as you have presented is that I have another userform in the file, and it looks like the below, with the text replaced with sample text.

As you can see it is not just a button, so I wanted to find code that would work on both so that the only difference I need to make between them is setting up their SetPos functions differently, and any functionality with their controls. The code also needed to work for any future userform I decide to add.

This is also why the code I showed in my previous reply had stuff that factored in multiple controls and their positions, despite the button only having the one control so I could have just done this instead:
Me.Width = CommandButton1.Width
Me.Height = CommandButton1.Height

I did download the demo, and it did work as per the animated gif.

 

[Jaafar Tribak]

@superstan2310

@Jaafar Tribak If you can somehow think of a way to get InsideWidth and InsideHeight to work, please let me know, otherwise, if by the end of the week there are no further responses, I will mark this thread as resolved and set Reply #6 as the resolution.

For a bare minimum code that removes the form caption and adjusts the form's Height & Width to its client area, you can give the following code example a try:

In the UserForm Module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        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 GetWindowLong Lib "USER32.DLL" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hwnd As LongPtr) As Long
    Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private hWndForm As LongPtr
 #Else
    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 GetWindowLong Lib "USER32.DLL" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hwnd As Long) As Long
    Private Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
    Private hWndForm As Long
 #End If

Private WithEvents WB As Workbook

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0&
    Set WB = ThisWorkbook
    Me.Width = Me.InsideWidth:     Me.Height = Me.InsideHeight
    Call IUnknown_GetWindow(Me, hWndForm)
    Call RemoveMenu
    With Application
        Call SetPos(.Width - 150& - .Left, .Height - 125&)
    End With
End Sub

Private Sub RemoveMenu()
    Const GWL_STYLE = (-16), WS_CAPTION = &HC00000
    Const GWL_EXSTYLE = (-20&), WS_EX_DLGMODALFRAME = &H1&, WS_EX_NOACTIVATE = &H8000000
    Call SetWindowLong(hWndForm, GWL_STYLE, _
         GetWindowLong(hWndForm, GWL_STYLE) And (Not WS_CAPTION))
    Call SetWindowLong(hWndForm, GWL_EXSTYLE, _
         GetWindowLong(hWndForm, GWL_EXSTYLE) And (Not WS_EX_DLGMODALFRAME) Or WS_EX_NOACTIVATE)
    Call DrawMenuBar(hWndForm)
End Sub

Private Sub SetPos(ByVal X As Double, Y As Double)
    Me.Left = X
    Me.Top = Y
End Sub

Private Sub WB_WindowResize(ByVal Wn As Window)
    With Application
        Call SetPos(.Width - 150& - .Left, .Height - 125&)
    End With
End Sub

@Dan_W

I saw your Unload_Macro routine - did you pass the Userform as a pointer because it's just not possible to pass it as an object via the commandbars OnAction / Parameter properties (I tried both methods, couldn't get it to work, which makes sense)?

Yes. I pass the userform pointer as you said ... I could have just as well stored the clicked button object in a public variable but I thought passing its pointer via the OnAction arguments is cleaner.

You say you couldn't make the OnAction work . Are you Right-Clicking the button ? The OnAction is for the context popup menu that comes up when right-clciking the floating button.


EDIT:
@superstan2310
Note that pressing ALT+F4 keys will unload the floating caption-less userfom. This is probably something that you don't want to happen. If you need to address this issue, you use the QueryClose event and its Cancel + Close Mode parameters.

 

[Dan_W]

You say you couldn't make the OnAction work . Are you Right-Clicking the button ? The OnAction is for the context popup menu that comes up when right-clciking the floating button.

Sorry, Jaafar, your code worked perfectly. What I meant was I couldn't get OnAction to work with my 'brilliant'(?) idea of just passing the UserForm object instead of a pointer.

I was looking at the Unload_Macro routine, and wondered why you were using a pointer instead of just passing the object ByRef... And so, as a proof of concept, I figured I would try it out by changing the procedure signature of the Unload Macro routine and then the relevant OnAction line to:

.OnAction = "'Unload_Macro " & Me & "'"

Now, obviously that was never going to work, but I was very sleepy at the time, and came up with another brilliant(?) idea, that I would just pass it using the Parameter member:

.Parameter = Me

Obviously, also did not work. Then it dawned on me why you were passing it as a pointer. Certainly much cleaner.

The moral of the story is - don't code when you're sleepy.

 

[Jaafar Tribak]

The moral of the story is - don't code when you're sleepy.

 

[superstan2310]

@superstan2310


For a bare minimum code that removes the form caption and adjusts the form's Height & Width to its client area, you can give the following code example a try:

In the UserForm Module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        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 GetWindowLong Lib "USER32.DLL" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hwnd As LongPtr) As Long
    Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private hWndForm As LongPtr
 #Else
    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 GetWindowLong Lib "USER32.DLL" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByRef hwnd As Long) As Long
    Private Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
    Private hWndForm As Long
 #End If

Private WithEvents WB As Workbook

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0&
    Set WB = ThisWorkbook
    Me.Width = Me.InsideWidth:     Me.Height = Me.InsideHeight
    Call IUnknown_GetWindow(Me, hWndForm)
    Call RemoveMenu
    With Application
        Call SetPos(.Width - 150& - .Left, .Height - 125&)
    End With
End Sub

Private Sub RemoveMenu()
    Const GWL_STYLE = (-16), WS_CAPTION = &HC00000
    Const GWL_EXSTYLE = (-20&), WS_EX_DLGMODALFRAME = &H1&, WS_EX_NOACTIVATE = &H8000000
    Call SetWindowLong(hWndForm, GWL_STYLE, _
         GetWindowLong(hWndForm, GWL_STYLE) And (Not WS_CAPTION))
    Call SetWindowLong(hWndForm, GWL_EXSTYLE, _
         GetWindowLong(hWndForm, GWL_EXSTYLE) And (Not WS_EX_DLGMODALFRAME) Or WS_EX_NOACTIVATE)
    Call DrawMenuBar(hWndForm)
End Sub

Private Sub SetPos(ByVal X As Double, Y As Double)
    Me.Left = X
    Me.Top = Y
End Sub

Private Sub WB_WindowResize(ByVal Wn As Window)
    With Application
        Call SetPos(.Width - 150& - .Left, .Height - 125&)
    End With
End Sub

@Dan_W

Yes. I pass the userform pointer as you said ... I could have just as well stored the clicked button object in a public variable but I thought passing its pointer via the OnAction arguments is cleaner.

You say you couldn't make the OnAction work . Are you Right-Clicking the button ? The OnAction is for the context popup menu that comes up when right-clciking the floating button.


EDIT:
@superstan2310
Note that pressing ALT+F4 keys will unload the floating caption-less userfom. This is probably something that you don't want to happen. If you need to address this issue, you use the QueryClose event and its Cancel + Close Mode parameters.

Hi Jaafar, This works perfectly, I completely forgot that a value can be different depending on when you set that value, so putting the InsideWidth/Height at the start before all the title bar shenanigans obviously ends up changing what is considered "Inside" hadn't even crossed my mind. I also love the extra call that was added to the RemoveMenu sub, getting rid of the extra minimal area around the userforms, making the button actually look like just the button. While I doubt that any of the users this file is planning to go to would end up using ALT+F4 for anything, I must thank you for pointing out an extra way that could have caused them to remove the form, and I have implemented the QueryClose event as suggested.

 

[Jaafar Tribak]

Hi Jaafar, This works perfectly, I completely forgot that a value can be different depending on when you set that value, so putting the InsideWidth/Height at the start before all the title bar shenanigans obviously ends up changing what is considered "Inside" hadn't even crossed my mind. I also love the extra call that was added to the RemoveMenu sub, getting rid of the extra minimal area around the userforms, making the button actually look like just the button. While I doubt that any of the users this file is planning to go to would end up using ALT+F4 for anything, I must thank you for pointing out an extra way that could have caused them to remove the form, and I have implemented the QueryClose event as suggested.

I am glad you have solved this and thanks for the feedback