Solved

Excel API Call to Give Focus to Input Box

Posted on 2010-09-15
2
1,384 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:glennes
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:glennes
ID: 33684183
Well how about that! Didn't know it was so easy to do!

Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now