PDA

View Full Version : [SOLVED] Position modeless userform to top right of Activewindow



Paul_Hossler
10-15-2013, 08:28 AM
Working on a 'Wizard' form (below is just some test code as a proof of concept)

I'd like the modeless UF to align top right of the active pane, but can't get the .Top's and .Left's and .Width's to work out reliably in the UserForm_Activate

The screen shot show what I'm trying to achieve (it was manually positioned since I can't get the code the right)

I'd like to set the height to the same as the ActiveWindow also




Option ExplicitPrivate Sub CommandButton1_Click()
ActiveSheet.Range("A1").Value = Format(Now, "long date") & " - " & Format(Now, "long time")
End Sub
Private Sub CommandButton2_Click()
Load ufData
ufData.Show (vbModal)
End Sub
Private Sub CommandButton3_Click()
ActiveSheet.Range("A2").Value = 123
End Sub
Private Sub CommandButton4_Click()
Me.Hide
Unload Me
End Sub
Private Sub UserForm_Activate()
Me.StartUpPosition = 0
Me.Top = -Application.Top - ActiveWindow.Top
Me.Left = -ActiveWindow.Left + ActiveWindow.Width - Me.Width
End Sub




Paul

Doug Robbins
10-16-2013, 01:56 AM
I am not sure that there is anyway of determining what values to use to get the form to open in that position.

You could put a temporary command button on the form that runs the following code and then position the form where you want it and click on that button and record the values and then use them in the Activate Event.


MsgBox Me.Top
MsgBox Me.Left

Of course that won't be of much use if you are creating a wizard that will be used by various users of various machines.

You might also take a look at the following page Chip Pearson's website:

http://cpearson.com/excel/FormPosition.htm

Another approach would be to give the use instructions to drag the form to the position that they desire and then have one of your command buttons write the Top and Left values to the Registry using SaveSetting and have a GetSetting routine in the userform Activate to get the Top and Left values from the registry and use them thereafter to automatically position the form.

snb
10-16-2013, 04:11 AM
I managed with


Private Sub UserForm_Initialize()
startupposition = 0
Application.WindowState = xlMaximized
Top = Application.Height * 0.11
Left = (Application.Width - Width) * 0.97

' Application.Windows(1).DisplayVerticalScrollBar
' Application.Windows(1).DisplayRuler
' Application.Windows(1).DisplayHeadings
' Application.DisplayFormulaBar
End Sub

But I think it's also dependent of the verticalscrollbar, the ruler, the display of headings and the formulabar.

Nor the properties of application.windows(1), nor those of Application.windows(1).activepane were helpful

Paul_Hossler
10-16-2013, 06:25 AM
@Doug -- I had looked at Chip's also and that will position the UF based on a cell, which wouldn't work to have something that would look like a task pane

@snb -- you're right about many of the display-oriented properies not being helpful. I'm reluctant to hard code fudge factors (0.11, 0.97, etc.) since on a different machine, the UF could be only partially displayed, or might cover up something else

Back to the drawing board. :banghead::banghead:

I might have to go with a 'Wizard' tab on the ribbon which would not be as user friendly as the modeless userform approach, but might be the most flexible way :crying:

Paul

Aflatoon
10-16-2013, 07:55 AM
This should always appear below the formula bar (if shown, or Ribbon if not). The -10 is to allow for window borders, though you could probably get that information from the registry if necessary!

Option Explicit
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

'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" ( _
ByVal hWnd As Long) As Long


Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hdc As Long, _
ByVal nIndex As Long) As Long


Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hWnd As Long, _
ByVal hdc As Long) As Long

Private Declare Function GetWindowRect Lib "user32.dll" ( _
ByVal hWnd As Long, lpRect As RECT) As Long
Private Type RECT
left As Long
top As Long
right As Long
bottom As Long
End Type
Private Const LOGPIXELSY = 90

'The width of a Y pixel in Excel's userform coordinates
Private Function PointsPerPixelY() As Double
Dim hdc As Long
hdc = GetDC(0)
PointsPerPixelY = 72 / GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc
End Function

Private Sub UserForm_Activate()
Dim rectBk As RECT
Dim hWndBk As Long
hWndBk = GetWorkbookHandle(ActiveWorkbook.Name)
GetWindowRect hWndBk, rectBk
Me.top = rectBk.top * PointsPerPixelY
Me.left = Application.left + Application.Width - Me.Width - 10
Me.Height = (rectBk.bottom - rectBk.top) * PointsPerPixelY
End Sub

Function GetWorkbookHandle(strWBCaption As String, Optional lngHWnd As Long) As Long
Dim hWnd As Long, hWndDesk As Long
Dim strText As String
If lngHWnd = 0 Then lngHWnd = Application.hWnd
hWndDesk = FindWindowEx(lngHWnd, 0&, "XLDESK", vbNullString)
If hWndDesk <> 0 Then
hWnd = FindWindowEx(hWndDesk, hWnd, "EXCEL7", strWBCaption)
If hWnd <> 0 Then GetWorkbookHandle = hWnd
End If
End Function

Jan Karel Pieterse
10-16-2013, 09:15 AM
You could of course tile the userform next to the application window (so Excel resizes), a bit like this screenshot.

Kenneth Hobs
10-16-2013, 09:26 AM
I did it similar to Aflatoon's code but with some assumptions.

In a Module:


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 GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long

'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hdc As Long, _
ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, _
ByVal hdc As Long) As Long

Public Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Const LOGPIXELSY = 90

'The width of a Y pixel in Excel's userform coordinates
Public Function PointsPerPixelY() As Double
Dim hdc As Long
hdc = GetDC(0)
PointsPerPixelY = 72 / GetDeviceCaps(hdc, LOGPIXELSY)
ReleaseDC 0, hdc
End Function

Function RectVScrollBar() As RECT
Dim xlmain As Long, xldesk As Long, excel As Long
Dim nuiscrollbar As Long, netuihwnd As Long
xlmain = FindWindow("xlmain", vbNullString)
xldesk = FindWindowEx(xlmain, 0&, "xldesk", vbNullString)
excel = FindWindowEx(xldesk, 0&, "excel7", vbNullString)
nuiscrollbar = FindWindowEx(excel, 0&, "nuiscrollbar", vbNullString)
netuihwnd = FindWindowEx(nuiscrollbar, 0&, "netuihwnd", vbNullString)
Dim TheRect As RECT
' size and position values stored in rect
GetWindowRect netuihwnd, TheRect
RectVScrollBar = TheRect
End Function

Function HBar() As RECT
Dim xlmain As Long, excelh As Long
xlmain = FindWindow("xlmain", vbNullString)
excelh = FindWindowEx(xlmain, 0&, "excelh", vbNullString)
Dim TheRect As RECT
' size and position values stored in rect
GetWindowRect excelh, TheRect
HBar = TheRect
End Function



In the Userform2 or change the name Userform2 to your Userform's name:

Private Sub UserForm_Activate()
Dim vsbar As RECT
vsbar = RectVScrollBar

With UserForm2
.Left = vsbar.Left * PointsPerPixelY - .Width
.Top = HBar.Bottom * PointsPerPixelY
End With
End Sub

Paul_Hossler
10-16-2013, 09:39 AM
You could of course tile the userform next to the application window (so Excel resizes), a bit like this screenshot.

@JKP --Can you tile a userfrom like that using VBA? If so, could you post a sample please?

@KH and Aflatoons -- Graduate level stuff. I'll have to experiment, but it looks like it's much closer to a solution than what I came up with

Paul

Jan Karel Pieterse
10-16-2013, 11:28 AM
Yes of course you can. I'll try to dig up an example tomorrow.

Jan Karel Pieterse
10-17-2013, 01:17 AM
Please find attached a demo. Note that this only works when the form is shown modeless, if not, you have to do the tiling before actually showing the form.

Paul_Hossler
10-17-2013, 02:27 PM
@JKP -- thanks for the example

@KH -- thanks for the PM and the other API insights


So many choices, but it's much better than not having any choices at all for sure

Now all I have to do is come up with the Wizard

Paul