Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel API Call to Give Focus to Input Box

Posted on 2010-09-15
2
Medium Priority
?
1,518 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

722 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