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

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
Kevin SnelgroveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chinmay PatelChief Technology NinjaCommented:
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.
Kevin SnelgroveAuthor 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 NinjaCommented:
Yes. The macro was to be created in excel only. Please post a screenshot.
Determine the Perfect Price for Your IT Services

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

Kevin SnelgroveAuthor 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 NinjaCommented:
Delete everything and just paste my code.
Chinmay PatelChief Technology NinjaCommented:
PS: LOL on Crime Scene.. Just.. I just woke up. So..
Kevin SnelgroveAuthor 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 NinjaCommented:
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.
Kevin SnelgroveAuthor 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?
Chinmay PatelChief Technology NinjaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin SnelgroveAuthor 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!!!!!
Kevin SnelgroveAuthor Commented:
Thanks Chinmay! Well done!!
Chinmay PatelChief Technology NinjaCommented:
: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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.