Solved

Excel API Call to Give Focus to Input Box

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

Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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