Keep Workbook Window On Top

[Sheeloo]

I have a workbook which I open (in a new instance of Excel) at the
right-bootom of the screen... I want to keep it on top of all other windows
as long as it is not closed by me.

I need help to accomplish this. I have tried the SETWINDOWSPOS but could

I want to work it in both Excel 2007 and 2003.Also can you recommend any
add-ins and/or tools (freeware or otherwise) which are free of
spyware/malware?

Thanks in advance,
Sheeloo

 

[Chip Pearson]

Try code like the following. You can play around with the values of
XLWidth and XLHeight to get the position as you desire.



Public Declare Function SetWindowPos Lib "user32" ( _
ByVal HWnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
Public Declare Function GetSystemMetrics Lib "user32.dll" ( _
ByVal nIndex As Long) As Long
Public Declare Function MoveWindow Lib "user32" ( _
ByVal HWnd As Long, ByVal x As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal bRepaint As Long) As Long

Public Const HWND_TOPMOST = -1
Public Const SWP_NOMOVE = &H2
Public Const SWP_NOACTIVATE = &H10
Public Const HORZRES = 8
Public Const VERTRES = 10
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1

Dim XLApp As Excel.Application

Sub AAA()

Dim HWnd As Long
Dim ScreenX As Long
Dim ScreenY As Long
Dim XLLeft As Long
Dim XLWidth As Long
Dim XLTop As Long
Dim XLHeight As Long

Set XLApp = New Excel.Application
XLApp.Visible = True

ScreenX = GetSystemMetrics(SM_CXSCREEN)
ScreenY = GetSystemMetrics(SM_CYSCREEN)

XLWidth = 200
XLLeft = ScreenX - XLWidth
XLHeight = 150
XLTop = ScreenY - XLHeight
HWnd = XLApp.HWnd
XLApp.WindowState = xlNormal
SetWindowPos HWnd, HWND_TOPMOST, XLLeft, XLTop, _
XLWidth, XLHeight, SWP_NOMOVE + SWP_NOACTIVATE
MoveWindow HWnd, XLLeft, XLTop, XLWidth, XLHeight, True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sheeloo]

Hello Chip,

Thanks a lot. It works exactly as I wanted.

I will compare with what I had tried to find out what I was doing wrong...

Just wanted to tell you that I have learnt a lot from your articles on your
website... Thanks again for sharing your wisdom.

Regards,
Sheeloo