Freeze userform position relative to worksheet

[tbrock]

Hello all,

I have a spreadsheet (Windows 32-bit, Excel 2007) in which I have a userform successfully being placed where I want it (top left of cell B1).

But the userform remains fixed in its initial position when I reposition the spreadsheet.

I would like the userform to move with the spreadsheet as if it is welded to its initial startup position. So I would always be able to see what I see in the first photo regardless of where on the screen Excel is.

I have looked high and low - after a few hours, I was frustrated/determined enough to scroll thru all the pages here at Mr. Excel that came up in search. So far nothing for what I hope (and believe) is a trivial problem. Can some kind soul prove me correct?

Thank you,
Tom

 

[tbrock]

Kind sirs, I apologize for my silence. I am still not accustomed to the holidays here (Dominican Republic) and it turns out that yesterday was Father's Day here. Today has been busy. I started playing with this about an hour ago. I think I am close to getting the code from Chip Pearson to work. But I am getting groggy and am heading to bed. I'll give it another shot tomorrow if I am able (have a deadline looming).

AND I will be upset at you Dataluver if it turns out that your (much shorter) code gets the job done! Though it is also important to me that the userform move with the spreadsheet - not just with resizing the spreadsheet. I tested the Chip Peason example that Logit posted and the "Child of Application option" looks to be the option I need - although it has the tiniest bit of drift when maximizing the window. As you noted, it most certainly is not trivial! With you folks, I am standing on the shoulders of giants!

 

[tbrock]

Without posting all my code, might anyone have an idea why this following line of code moves the userform?

The code which produced the correct positioning of my form was correctly positioning the userform on startup (as seen here):

So I added in code from the Chip Pearson example after the userform's initial positioning. In the snippet below, using Chip's code, the userform is successfully assigned to be a child of the worksheet (not the workbook or application). The form maintains its position in relation to the the worksheet, it miniminizes when that particular worksheet is minimized, etc. But as setting the parent occurs, there is a side effect (as noted in the comments):

'<=== Form IS correctly positioned here
Res = SetParent(hWndChild:=ChildHWnd, hWndNewParent:=ParentHWnd)
'<=== Form is NOT correctly positioned here

The userform's postion jumps! I am guessing that with the userform being successfully set as a child of the worksheet, that the measurement "zero position" changed and/or the units of measure changed.

What is even stranger to me (and perhaps it is something elsewhere in the code that broke this, but [just in case]) is that after this point, I am getting very nonintuitive results from assigning .top and .left of the userform. For example, changing .top moves the userform horizontally. Changing .left moves the userform vertically. Also, if I manually set userform.left = 20...and then view the value of userform.left in the immediate window, it is has instead been set to something (seemingly, though I know better) random.

 

[tbrock]

As an example of not getting the .top and .left results expected after making the userform a child of the worksheet, this is from the Immediate Window: There seems to be a fairly uniform pattern.

I tried several assigns.

For .top, whatever value I key is being assigned as (Toms_Value + 140.75) [At times, +141]

• 40 becomes 180.75
• 100 becomes 240.75
• 120 becomes 261
• 160 becomes 300.75

For .left, whatever valie I key is being assigned as (Toms_value + 104.75). [At times, +105]

• 10 becomes 114.75
• 40 becomes 144.75
• 64 becomes 168.75

 

[Legacy 456155]

Just a guess that you should initialize your userform, set its parent, move it into position, and then show it.

Dim f As UserForm1
Set f = New UserForm1
'SetParent, etc. here
f.Show

 

[tbrock]

I know I am showing the form first so that I could see the results as they occurred.

I am on the road until Saturday, I'll see if that makes a difference then.

 

[Legacy 456155]

Enjoy your road trip!

 

[tbrock]

Ha! If only....

I'm parked in my sistet-in-law's apartment (4.5 hours from home) while the pickup truck is in the shop...last night without eletricity or running water...life in the DR...electricty/water is not permanent for many and if you live in a remote area as I do, many things require a long journey to a city.

 

[Legacy 456155]

Oh? You guys are dealing with a tropical storm right about now, aren't you?
" ...life in the DR... "
Better than Haiti! Or Portland.

 

[tbrock]

Oh? You guys are dealing with a tropical storm right about now, aren't you?
" ...life in the DR... "
Better than Haiti! Or Portland.

It is just arriving in Santo Domingo. Heavy drizzle for the last few hours. It seems it is going to get wet and breezy here.

 

[tbrock]

Ouch. I am going nowhere. Fast. Delaying .Show until the last step did not change anything. Went back to testing manually assigning of position. These are the first results of that:

1. set frm.Top = 100 --> Immediate window shows top as 281 (I believe).
2. set frm.Top = 10 --> Immediate window shows top as 147....AND the form disappeared! because... .Left got changed to 1637.25!

I admit to being...lost!

Something that I believe is an important note. I originally had this spreadsheet WITHOUT the code from Chip Pearson (which makes the userform a child of the worksheet). At that time, the initial positioning from Yin Cognyto WAS functioning correctly and displayed the userform over cell A1 as desired. However that position was tied to the screen and not to the worksheet. So when the spreadsheet was moved the userform (contrary to my wishes) stayed in the same place. Adding the Pearson code fixed that second issue (the userform now moves not just with the workbook but with the workseet as I desire). I had debugging showing that the userform was in the correct postion until the line executed making the userform a child of the worksheet. Before executing the next line of code, the userform jumped to a different location! (Sigh) I have since played with the order in which the

1. "Hide Menu Bar"
2. "Set Initial position code"
3. "Make a Child of"

code segments are executed without noticing a difference in result. If I have all 3 code segments, the userform ends up in the incorrects spot. I believe it is more correct to say if I have the "Set Initial Position" and "Make a Childe of" code both included, the userform ends up in the incorrect spot. I do not believe the "Hide Menu Bar" affects the issue.

Can anyone find the error or provide the missing piece to the puzzle?

I uploaded a copy the spreadsheet (Excel 2007) . It is stripped down to only include what is pertinent to this issue. Unless I did something careless, it should be functioning as intended except that the userform frsSearchTags shows in the incorrect spot (it should have the same appear as if it has the same .top and .left as cell A1. Anyway, here is the link:

GameDev - Stripped down.xlsm

drive.google.com

Note: I have two places with debug.print which clearly can be removed if desired. I use them just to document what is actually happening when I assign .Left and .Top

Here is the code from the stripped down version of the spreadsheet.

First the code module ModGameDev:

    Option Explicit
    Option Compare Text

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin Yin Cognyto
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Private Const LOGPIXELSX = 88
    Private Const LOGPIXELSY = 90
    Public Type pointcoordinatestype
        Left As Double
        Top As Double
        Right As Double
        Bottom As Double
    End Type
    Private pixelsperinchx As Long, pixelsperinchy As Long, pointsperinch As Long, zoomratio As Double
    #If VBA7 And Win64 Then
        Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long
        Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
        Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hdc As LongPtr) As Long
    #Else
        Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
        Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
        Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long
    #End If
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End Yin Cognyto
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Yin Cognyto Begin
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ConvertUnits()                                                 '< Yin Cognyto
  Dim hdc As Long         '< Yin Cognyto had type as Longptr, but this threw a "type undefined" error
    hdc = GetDC(0)
    pixelsperinchx = GetDeviceCaps(hdc, LOGPIXELSX) ' Usually 96
    pixelsperinchy = GetDeviceCaps(hdc, LOGPIXELSY) ' Usually 96
    ReleaseDC 0, hdc
    pointsperinch = Application.InchesToPoints(1)   ' Usually 72
    zoomratio = ActiveWindow.Zoom / 100
End Sub

Private Function PixelsToPointsX(ByVal pixels As Long) As Double           '< Yin Cognyto
    PixelsToPointsX = pixels / pixelsperinchx * pointsperinch
End Function

Private Function PixelsToPointsY(ByVal pixels As Long) As Double            '< Yin Cognyto
    PixelsToPointsY = pixels / pixelsperinchy * pointsperinch
End Function

Private Function PointsToPixelsX(ByVal points As Double) As Long           '< Yin Cognyto
    PointsToPixelsX = points / pointsperinch * pixelsperinchx
End Function

Private Function PointsToPixelsY(ByVal points As Double) As Long           '< Yin Cognyto
    PointsToPixelsY = points / pointsperinch * pixelsperinchy
End Function

Public Sub GetPointCoordinates(ByVal cellrange As Range, ByRef pointcoordinates As pointcoordinatestype)   '< Yin Cognyto
  Dim i As Long
    ConvertUnits
    Set cellrange = cellrange.MergeArea
    For i = 1 To ActiveWindow.Panes.Count
        If Not Intersect(cellrange, ActiveWindow.Panes(i).VisibleRange) Is Nothing Then
            pointcoordinates.Left = PixelsToPointsX(ActiveWindow.Panes(i).PointsToScreenPixelsX(cellrange.Left))
            pointcoordinates.Top = PixelsToPointsY(ActiveWindow.Panes(i).PointsToScreenPixelsY(cellrange.Top))
            pointcoordinates.Right = pointcoordinates.Left + cellrange.Width * zoomratio
            pointcoordinates.Bottom = pointcoordinates.Top + cellrange.Height * zoomratio
            Exit Sub
        End If
    Next
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Yin Cognyto end
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Here is the code for the Workbook ThisWorkbook:

Private Sub Workbook_Open()
    frmSearchTags.Show vbModeless
End Sub

All the remainder of the code is in the userform frmSearchTags:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Begin Rick Rothstein @ ExcelFox.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'**** Start of API Calls To Remove The UserForm's Title Bar ****
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 move UserForm Around The Screen ****
'******************************************************************************
' Reenable these declarations AND the code in UserForm_MouseDown to enable       '<--- Currently enabled during debugging
' movement of frmSearchTags on 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

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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' End Rick Rothstein ExcelFox.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Being Chip Pearson
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function SetParent Lib "user32" ( _
    ByVal hWndChild As Long, _
    ByVal hWndNewParent As Long) As Long

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This declaration was done above in the code from Rick Rothstein.  Leaving it in place in case there is a need
' to copy only the code of Chip Pearson to another module - so that this code will be complete.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
'    ByVal lpClassName As String, _
'    ByVal lpWindowName As String) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long

Private Declare Function GetAncestor Lib "user32.dll" ( _
    ByVal hwnd As Long, ByVal gaFlags As Long) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' End Chip Pearson
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Private Sub cbTags_Change()
    Dim tags As String
    Dim tagsColumn As Range

    cbTags.HideSelection = True
    
    Set tagsColumn = Range("TagsColumn")
    tag = cbTags
    ActiveSheet.Range("TableData").AutoFilter Field:=tagsColumn.Column, Criteria1:="*" & tag & "*"

End Sub

Private Sub CommandButton1_Click()
    cbTags.SetFocus
    MsgBox "This functionality is disabled during testing"
End Sub

Private Sub CommandButton2_Click()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Turn off all filters and position begining of chart
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    cbTags.SetFocus
    frmSearchTags.cbTags = ""
    ActiveSheet.AutoFilterMode = False
    
End Sub

Private Sub UserForm_Initialize()
    Dim Style As Long, Menu As Long                                         '< Rick Rothstein @ ExcelFox.com
    Dim pointcoordinates As pointcoordinatestype, horizontaloffsetinpoints As Double, verticaloffsetinpoints As Double   '< Yin Cognyto

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin GrouchySmurf  <== Only using GrouchySmurf coding to try to find SOMETHING that works to assign position.
    '*                        Ultimately, only one code set from GrouchySmurf, Yin Cognyto or other would be user.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim AppXPoint, AppYPoint As Long
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End GrouchySmurf
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


    
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin Chip Pearson to make form a child of worksheet
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
    Dim AppHWnd As Long
    Dim DeskHWnd As Long
    Dim WindowHWnd As Long
    Dim MeHWnd As Long
    Dim Res As Long
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End Chip Pearson
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin Rick Rothstein @ ExcelFox.com to hide menu bar
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    hWndForm = FindWindow("ThunderDFrame", Me.Caption)
    Style = GetWindowLong(hWndForm, &HFFF0)
    Style = Style And Not &HC00000
    SetWindowLong hWndForm, &HFFF0, Style
    DrawMenuBar hWndForm
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End Rick Rothstein @ ExcelFox.com
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    
    
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin Chip Pearson to make form a child of worksheet
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    AppHWnd = FindWindow("XLMAIN", Application.Caption)
    
    If AppHWnd > 0 Then
        ' get the window handle of the Excel desktop
        DeskHWnd = FindWindowEx(AppHWnd, 0&, "XLDESK", vbNullString)
        If DeskHWnd > 0 Then
            ' get the window handle of the ActiveWindow
            WindowHWnd = FindWindowEx(DeskHWnd, 0&, "EXCEL7", ActiveWindow.Caption)
            If WindowHWnd > 0 Then
                ' ok
            Else
                MsgBox "Unable to get the window handle of the ActiveWindow."
            End If
        Else
            MsgBox "Unable to get the window handle of the Excel Desktop."
        End If
    Else
        MsgBox "Unable to get the window handle of the Excel Application."
    End If
    
    ' get the window handle of the userform
    MeHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)
    
    If (MeHWnd > 0) And (WindowHWnd > 0) Then
         ' make the userform a child window of the ActiveWindow
         Res = SetParent(MeHWnd, WindowHWnd)
         If Res = 0 Then
             ''''''''''''''''''''
             ' an error occurred.
             ''''''''''''''''''''
             MsgBox "The call to SetParent failed."
         End If
    End If
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End Chip Pearson
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''




    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin Yin Cognyto to position userform correctly
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    With Me
        ActiveSheet.Range("A1").Select                                      '<--Tom. To reset where the control form is located
        horizontaloffsetinpoints = (.Width - .InsideWidth) / 2
        verticaloffsetinpoints = 1
        Call GetPointCoordinates(ActiveCell, pointcoordinates)
        .StartUpPosition = 0
        .Top = pointcoordinates.Top - verticaloffsetinpoints
        .Left = pointcoordinates.Left - horizontaloffsetinpoints
    End With
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End Yin Cognyto
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
'<---- Begin Debugging
Debug.Print "Post Yin Gognyto:"
Debug.Print "-- left set to " & (pointcoordinates.Left - horizontaloffsetinpoints) & " --> result = " & Me.Left & ". Difference = " & (Me.Left - (pointcoordinates.Left - horizontaloffsetinpoints))
Debug.Print "-- top set to " & (pointcoordinates.Top - verticaloffsetinpoints) & " --> result = " & Me.Top & ". Difference = " & (Me.Top - (pointcoordinates.Top - verticaloffsetinpoints))
'<---- End Debugging
    
    
    

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Begin GrouchySmurf --> Another attempt at positioning userform
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'AppXPoint = Application.Left + (Application.Width - Me.Width)   '<---- GrouchySmurf
    'AppYPoint = Application.Top
    AppXPoint = Application.Left + 10     '<---- Tom Brock --> arbitrary postioning to test setting .left and .top
    AppYPoint = Application.Top + 126
    
    With Me
        .StartUpPosition = 0
        .Left = AppXPoint
        .Top = AppYPoint
    End With
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' End GrouchySmurf --> Another attempt at positioning userform
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'<---- Begin Debugging
Debug.Print "Post GrouchySmurf:"
Debug.Print "-- left set to " & AppXPoint & " --> result = " & Me.Left & ". Difference = " & (Me.Left - (AppXPoint))
Debug.Print "-- top set to " & AppYPoint & " --> result = " & Me.Top & ". Difference = " & (Me.Top - (AppYPoint))
'<---- End Debugging

    
    
    
    
    Me.Show
    
End Sub

Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'***************************************************************************************************
'    Begin Rick Rothstein @ Excelfox.com coding to allow movement of this (Userform2) with the mouse
'
'    I had this DISABLED but it is temporarily enabled to allow me to correctly position the userform
'***************************************************************************************************
    If Button = xlPrimaryButton Then
        Call ReleaseCapture
        Call SendMessage(hWndForm, WM_NCLBUTTONDOWN, HTCAPTION, ByVal 0&)
    End If
'***************************************************************************************************
'    End Rick Rothstein Excelfox.com coding
'***************************************************************************************************

End Sub