Solved

Excel API Call to Give Focus to Input Box

Posted on 2010-09-15
2
1,484 Views
Last Modified: 2012-05-10
I've got a VBA script that includes a line to open an input box for the user to enter a date. At the time the input box line executes, there is an open spreadsheet with other macros in it.  I created a rectangle box on the face of the open worksheet (Rectangle2) and put a line of static text in it ("process is running"). The first line of the VBA script calls another sub, DoIt, which puts this message box (Rectangle2) on the open spreadsheet telling the user that the process is running, and leaves that message box there until the entire VBA code finishes running, then removes it from the face of the open sheet. What should be happening after the sub that puts the message box (Rectangle2) on the spreadsheet finishes, the code should go back to the first sub and continue running it where it left off, with the open Excel sheet still showing, so that the VBA code can present an input box to the user to get a date, which is used later in the script. It does this correctly, except that it puts the input box behind the open spreadsheet instead of on top of it where the user can see it.

I don't know if this is because the sub that puts the 'process is running' message box (Rectangle2) on the open spreadsheet is not correctly returning control (and/or focus) back to the sub that subsequently creates and presents the input box to the user. Is there a line of code that needs to be entered in the message box sub that explicitly returns back to the first sub? Even without a line to explicitly do that, when I step through the program (F8), the 'next line to be executed - highligthed in yellow' - goes back to the first sub and picks up where it left off when the Call DoIt command was issued, and runs the line that creates the input box.

I've been told that there is a method using API calls that will force Excel to give focus to the input box (or to any open window) and thereby place it on top of the open spreadsheet. I'm unfamiliar with the use of API calls, so I don't know how to go about this if using API calls is the only way to give front focus to the input box.

If there is another way in VBA to force the input box to havefront focus and be visible on top of the open spreadsheet, I'd like to know how to code it to do so.

Thanks!

---------------------------------------

Here is the code for the two subs:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = True
Call DoIt
Dim sEndDate As String
Dim sUser As String
Dim sPassword As String
Dim sEnv As String

sEndDate = InputBox("Enter Date")
.
.      --more code---
.
End Sub

Sub DoIt()
Application.ScreenUpdating = True
With Sheet1.Shapes("Rectangle 2")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle 2").Visible)
End With
Sheet2.Select
Sheet1.Select
End Sub
0
Comment
Question by:Glenn Stearns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
insaneCFC earned 500 total points
ID: 33682431
You could try changing to this - sEndDate = Application.InputBox("Enter Date")
This way the Inputbox will remain on top until closed

Regards,
Kalata
0
 

Author Comment

by:Glenn Stearns
ID: 33684183
Well how about that! Didn't know it was so easy to do!

Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question