Consulting

Results 1 to 14 of 14

Thread: Solved: Sheet Positioning

  1. #1
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location

    Solved: Sheet Positioning

    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.

  2. #2
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  3. #3
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    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?

  4. #4
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by BlueCactus
    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?

    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
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  5. #5
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by BlueCactus

    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?
    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.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  6. #6
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    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]

  7. #7
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Thanks, BlueCactus! I appreciate you working through this, and sharing with the rest of us.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  8. #8
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Yes, thanx for that BlueCactus! 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
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  9. #9
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by johnske
    If it works ok, how about making it a submission for the knowledge-base?...
    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.

    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.

  10. #10
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  11. #11
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    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:

    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.
    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.

    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

  12. #12
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  13. #13
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    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.

  14. #14
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    OK, Sorry, 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
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •