How to force Microsoft Excel application to sit behind all other applications.

Kevin Snelgrove
Kevin Snelgrove used Ask the Experts™
on
I had this question after viewing Using VB6 - How to force my application to sit behind all other applications..  I'm want to implement this solution with Microsoft Excel... i.e. always keep my Excel documents on the background.  I'm a newbie when it comes to code... where would I put the code that was suggested by danaseaman in this post and what modifications would I need to make for Excel. Thanks! Kevin
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi Kevin,

Please record a macro and then use the following code
Option Explicit

Private Declare Function SetWindowPos Lib "user32.dll" (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

Private Const HWND_BOTTOM = 1
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1

  
Sub SendExcelToBack()
'
' SendExcelToBack Macro
'
' Keyboard Shortcut: Ctrl+m
'
   SetWindowPos ActiveWindow.hwnd, HWND_BOTTOM, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE

End Sub

Open in new window



Regards,
Chinmay.

Author

Commented:
Hi Chinmay,

Firstly, Thanks for responding! I gave this a go... I assumed that you wanted me to create/record a macro in excel.  If that's not correct, I don't know how to create Macros outside of Excel (but willing to learn!).  Anyway,  I created the following simple excel macro:
 
Sub SendExcelToBack()

End Sub

And then I pasted what you gave me but but I get the following error:

Compile error: Only comments may appear after End Sub, End Function or End Property

and then it highlights this line:

Private Declare Function SetWindowPos Lib "user32.dll" (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

Let me know how you think I should proceed and again thanks!
Kevin
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Yes. The macro was to be created in excel only. Please post a screenshot.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
See attached. I took shots before and after.  Feels like a crime scene!  Thanks again!!
Inital-Macro-before-pasting.PNG
Macro-after-pasting.PNG
error-message.PNG
Macro-with-highlighted-field-after-g.PNG
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Delete everything and just paste my code.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
PS: LOL on Crime Scene.. Just.. I just woke up. So..

Author

Commented:
Glad you liked that and Good morning! Okay Macro works (sorry about the confusion!) but I need the excel window to permanently stay in the back so if I click on excel it wont come to the front and cover up my other applications... see images attached.  The current/actual state where when I click on excel it will come to the front of my screen thus covering my internet browser.  The desired state would be to click on excel and it would stay in the back.   I'm just using explorer as an example otherwise I would use some great softwares I found that force apps to the top... i.e. deskpin, turbotop and autohotkey which work great on explorer and pretty much anything... but not on the application I need it to... hence looking to find a solution that would send excel to the back permanently!
actual-state.PNG
desired-state.PNG
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi Kevin,

I am sorry I don't think I know any API that will do it for us. These other softwares might have used some API that I am not aware of.
How about minimizing the excel or reducing its area?

Regards,
Chinmay.

Author

Commented:
unfortunately that's not an option as I could reduce to the size of one cell size but I still have the issue of the excel ribbon.... which you can get rid of via "auto hide ribbon" .... but that auto maximizes the window to full screen which would then cover all my other applications... when you go to minimize the screen, ribbon comes back... do you know a trick to have a minimized window setting with the auto hide ribbon enabled?
Chief Technology Ninja
Distinguished Expert 2018
Commented:
Try this

Option Explicit
Sub SendExcelToBack()
'
' SendExcelToBack Macro
'
' Keyboard Shortcut: Ctrl+m
'
    CommandBars.ExecuteMso "HideRibbon"
        With Application
        .WinowState = xlNormal
        .Width = 10
        .Height = 10
   End With
End Sub

Open in new window

Author

Commented:
Yes yes yes.... and yes!  the code had a typo WinowState  vs WindowState so I updated that since it didn't work at  first ....(I imagine Winow would get you an Nintendo Wii immediately)... Once I corrected the code, it wasn't working fully as expected at first ... because I soon as I stretched the window to a certain size... it would bring the ribbon back at top... I figured out by changing Width and Height to 100 each in the code, it allowed me to make it even bigger and keep the ribbon off!  AWESOME... thanks for sticking with it!!!!!

Author

Commented:
Thanks Chinmay! Well done!!
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
:D I have no clue how it became Winow :P I am supposed to conduct some in-person trainings soon so I switched keyboards (From Razer BlackWidow Ultimate to the laptop's built-in one - but that is no excuse) and I think I am having some issues as it has been a while since I used this built-in keyboard.

And that size, for me size 100 didn't give the results you wanted (I am using a full hd screen 1920x1080) and hence I switched to 10. Please keep that in mind if in future you also face some challenges. The window size did switch to 100x100 I could always see some controls of the excel when I tested it with 100x100. For me 10x10 worked like a charm. I couldn't see anything else but the min/max/restore buttons.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial