PDA

View Full Version : Sleeper: ActiveWindow Top and Left bug?



Pog1
11-03-2017, 10:47 AM
Hello.

I've written two functions to return the x and y coordinates for a userform which is to positioned relative to the active window. This works on Excel 2010 (Windows) and 2011 (Mac) but when I try it on 2016 with the ActiveWindow positioned halfway down the screen I get the value '2' returned for its .Top and .Left values!? Is this a recognised bug in 2016? Any workaround? Application.Top and .Left yields 0.

Thanks for reading.

Perhaps I should return to 2016 when Microsoft gets around to finishing it.

Radial
12-17-2023, 09:06 AM
It's been more than seven years since your post, Pog1. That bug is still here in MS Excel 360 for Mac in 2023. It seems that when one sets the ActiveWindow.Left property, it sets the Left property (good), then also uses the value previously used to set some other property (that was just hanging around) to alter the Top property (bad). In addition, setting the Top property to X puts the top of the window somewhere near X-200 pixels from the top of your screen (okay). However, getting the Top property returns a value that reflects the position of the bottom of the window from the bottom of your screen (huh?). So if you wanted to position a window B so that its top matches the top of a window A, you can't get the Top from window A and use that value to set the Top of Window B (yikes!)

Here's my workaround for positioning a window using VBA:


With ActiveWindow
.Height = 300
.Width = 400
.Left = <left position>
.Top = <top position>
.Height = <height value>
.Width = <width value>
End With

First, we small-ify the window so that the following moves don't make the window go off the screen. (Going off the screen messes up positioning.) Then, we position the left side of the window, which works for the left side of the window, but botches the location of the top of the window. Then, we position the top of the window, covering up the bug in the previous step. Finally, we set the desired height and width. Also, I think Application.ScreenUpdating must be True during this, but I'm not sure.

Pog1
12-17-2023, 09:52 AM
It's been more than seven years since your post, Pog1. That bug is still here in MS Excel 360 for Mac in 2023. It seems that when one sets the ActiveWindow.Left property, it sets the Left property (good), then also uses the value previously used to set some other property (that was just hanging around) to alter the Top property (bad). In addition, setting the Top property to X puts the top of the window somewhere near X-200 pixels from the top of your screen (okay). However, getting the Top property returns a value that reflects the position of the bottom of the window from the bottom of your screen (huh?). So if you wanted to position a window B so that its top matches the top of a window A, you can't get the Top from window A and use that value to set the Top of Window B (yikes!)

Here's my workaround for positioning a window using VBA:


With ActiveWindow
.Height = 300
.Width = 400
.Left = <left position>
.Top = <top position>
.Height = <height value>
.Width = <width value>
End With

First, we small-ify the window so that the following moves don't make the window go off the screen. (Going off the screen messes up positioning.) Then, we position the left side of the window, which works for the left side of the window, but botches the location of the top of the window. Then, we position the top of the window, covering up the bug in the previous step. Finally, we set the desired height and width. Also, I think Application.ScreenUpdating must be True during this, but I'm not sure.

Great work! Crazy it's not yet sorted, but not sure Microsoft's heart is really in it...!