Anyone know how to get (what would be) .Top and .Left of the ActiveSheet relative to the screen corner? The obvious properties seem to be Windows-specific.
Printable View
Anyone know how to get (what would be) .Top and .Left of the ActiveSheet relative to the screen corner? The obvious properties seem to be Windows-specific.
Hi BlueCactus,
Try this (change from 1 to what you want)[vba]Sub PositionIt()
With Application
'<<Set values for the application window>>
'//POSITION WITH RESPECT TO MONITOR
.Top = 1 '< points
.Left = 1 '< points
With .ActiveWindow
'<<Set values for the book inside the application>>
'//POSITION WITH RESPECT TO APP. WINDOW
.Top = 1 '< points
.Left = 1 '< points
End With
End With
End Sub[/vba]
That's what would make sense. However, in Excel X, Application.Left and Application.Top are always zero. It even says so in VBA Help. So those two are useless.
This is a problem because I want to ensure that a UserForm is aligned with content on a sheet (so that I know what is visible). UserForm.Top and .Left are relative to the screen corner.
For example, UserForm.Left = ActiveSheet.Columns("A").Left will always align to the left edge of the screen regardless of where the activewindow is located, because Application.Left is always zero.
What a pain, eh?
Quote:
Originally Posted by BlueCactus
I have 2K, so I can't speak for 2002, but yes, Left & Top are 0 (I only put it as 1 as an example) and what I gave aligns the application and/or the book - which is what I thort you wanted.
If you look in your UserForm properties you'll find what you can use - for example: Windows Default is 3 (Top LH of screen).
If you search the VBAX knowledge base I'm pretty sure you'll find a solution somwhere there (from memory I think it was to do with the start-up position of a userform being memorized - read it a few months back)
Regards,
John
Even more of a pain is that if for some reason you have to use cross-platform, UserForms are really difficult to adjust so that they look acceptable on other computers. I had helped someone who produced the workbook with UserForms in XL 2000, but needed it to work with XL 2001 and XL 2004 (Macs), which I have at home. It took many tries before even viewable UserForms were useable.Quote:
Originally Posted by BlueCactus
OK, I've worked it out. The trick is to use AppleScript to return the window position. The following function WindowPos(windowname as string) will return an array containing the four elements .Left, .Top, .Width, .Height for the named window. If the named window doesn't exist, it'll return a string "error". The subroutine Main() is just a demonstration for calling WindowPos().
Note for setting up the AppleScript that a double quote can be inserted into a string by using a pair of double quotes.
This script also works for Word by substituting the application name in the AppleScript.
[VBA]Sub Main()
Dim WinRect As Variant, TempText As String, WinName As String
WinName = ActiveWorkbook.Name
WinRect = WindowPos(WinName)
If IsArray(WinRect) Then
TempText = WinName & ".Left : " & WinRect(0) & " px" & Chr(13)
TempText = TempText + WinName & ".Top : " & WinRect(1) & " px" & Chr(13)
TempText = TempText + WinName & ".Width : " & WinRect(2) & " px" & Chr(13)
TempText = TempText + WinName & ".Height : " & WinRect(3) & " px"
MsgBox TempText
Else
MsgBox "Error"
End If
End Sub
Function WindowPos(WinName As String) As Variant
Dim Script1 As String, ScriptRet As String
Dim x As Variant, y As Variant, w As Variant, h As Variant, i As Integer
Script1 = "tell application ""Microsoft Excel""" & Chr(13)
Script1 = Script1 + "try" & Chr(13)
Script1 = Script1 + "set rect to bounds of window """ & WinName & """" & Chr(13)
Script1 = Script1 + "on error" & Chr(13)
Script1 = Script1 + "set rect to ""error""" & Chr(13)
Script1 = Script1 + "end try" & Chr(13)
Script1 = Script1 + "end tell" & Chr(13)
Script1 = Script1 + "return rect"
' ScriptRet will be of the general format "xx1, yy1, xx2, yy2".
' ScriptRet will be set to "error" if the window name is bad.
ScriptRet = MacScript(Script1)
If ScriptRet <> "error" Then
x = Val(Left(ScriptRet, InStr(ScriptRet, ",") - 1))
i = InStr(ScriptRet, ",") + 1
y = Val(Mid(ScriptRet, i, InStr(i, ScriptRet, ",") - i))
i = InStr(i, ScriptRet, ",") + 1
w = Val(Mid(ScriptRet, i, InStr(i, ScriptRet, ",") - i)) - x
i = InStr(i, ScriptRet, ",") + 1
h = Val(Mid(ScriptRet, i, Len(ScriptRet))) - y
WindowPos = Array(x, y, w, h)
Else
WindowPos = "error"
End If
End Function
[/VBA]
Thanks, BlueCactus! I appreciate you working through this, and sharing with the rest of us.
Yes, thanx for that BlueCactus! :thumb Always nice to learn new tricks. I haven't had a chance to look at it or try it out yet though.
If it works ok, how about making it a submission for the knowledge-base?...:)
Regards,
John
Yeah, I'll probably do that once I feel like I know enough about how Office uses pixel/point dimensions for different windows. Both Win and Mac are a pain the butt about this, and of course they're not quite the same.Quote:
Originally Posted by johnske
shades, one thing that might be useful for your forms is the Userform.Zoom property (in Userform_Initialize).
For example, you can use:
[vba]#If Mac then
Userform.Zoom = 120
Userform.Width = Userform.Width *1.2
Userform.Height = Userform.Height *1.2
#end if[/vba]
Doesn't fix everything, but will help make frame captions and combo boxes legible, radio buttons round, and stufff like that. At least gives you a start so that you don't have to change every property in the form. Remember to change Userform.Width and .Height to match, because that's not automatic.
Hi BlueCactus,
I've never really looked at this particular problem before, but I just tried this:
1) Insert the following code in your userform module
2) Show the form
3) Move the form to wherever you like and click it
4) Unload the form
5) Show the form again.
The form should be at the same position (relative to the screen) that it was when it was unloaded.
By further experiment, you will see that Top=0 and Left=0 positions this in the top left of the screen.
Now, used in conjunction with the code I gave you above for positioning the application )where Top=0 and Left=0 positions the application at the top Left of the screen) you can now always specify the startup position of both the application and userform relative to the screen, this means - you can specify the start-up position of the userform relative to the application in this simple manner.
Regards,
John
EDIT: Also, look here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=163 for an example of preventing the userform from being moved from the startup position...
Sorry, had to leave the thread to get the above link, here's the code I mentioned[vba]Option Explicit
Private Sub UserForm_Activate()
UserForm1.Top = Range("A1")
UserForm1.Left = Range("A2")
End Sub
Private Sub UserForm_Click()
Range("A1") = UserForm1.Top
Range("A2") = UserForm1.Left
End Sub
[/vba]
Hi John,
Userform.Top and .Left are not problematic - they are always relative to screen top left and can be set correctly as long as Userform.StartupPosition = 3 prior to Userform.Show
The problem is that Window.Left, .Top are not relative to screen top left. If you look up Application.Top on Excel X VBA Help, this is what you get:
So, Application.Left, .Top is not useful on Office X, and maybe not Office 2004 either. That's why I created the code above - it returns the position of an Office window w.r.t. the screen top left so that you can work out where objects in the window are relative to the screen top left, and your forms.Quote:
Application The distance from the top edge of the screen to the top edge of the main Microsoft Excel window. In Windows, if the application window is minimized, this property controls the position of the window icon (anywhere on the screen). On the Macintosh, the value is always 0 (zero); setting the value to something else will have no effect.
Now, I'm going to look into the stuff in your last post, because some of that (e.g., locking the form position) could also be useful to me.
Cheers,
BC
Hi BlueCactus,
Perhaps I misunderstood you here - Are you using Mac? Is that what this problem relates to? Because otherwise Application top and left are always relative to the screen, and Active window Top and left are relative to the application.
Regards,
John :hi:
Yep, that's the issue. I have to pin this down to a bit of laziness on Microsoft's part. After all, Excel has to know where it is, even if it doesn't get copied to Application.Left, .Top.
Fortunately MS have built in AppleScript support, allowing for workarounds to this and other issues.
OK, Sorry, :blush I don't have a mac, am not familiar with the issues peculiar to them and it seems I got off on the wrong foot with you there.
My Apologies,
John