Solved

Excel API Call to Give Focus to Input Box

Posted on 2010-09-15
2
1,409 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

786 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