ensure focus is on an InputBox

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

ensure focus is on an InputBox

Post by stuck »

The first time round my loop 'InputBox' behaves as expected, that is to say an input box appears, pre-populated with a default value and the focus is on that value, meaning the user can interact with the input box straight away.

The next time round the loop though, the input box appears and is pre-populated with a default vale BUT the focus is NOT on the input box. The user has to first select the input box. Only then can they interact with the box :scratch:

How do I ensure the second time the input box is presented focus is in the input box?

Thanks,

Ken

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ensure focus is on an InputBox

Post by HansV »

Could you post the relevant part of the code, or attach a sample workbook?
Best wishes,
Hans

User avatar
Charles Kenyon
4StarLounger
Posts: 596
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: ensure focus is on an InputBox

Post by Charles Kenyon »

It probably will not matter, but it would be a good idea to also post your version of Word and the Operating System. Sometimes, these do matter.

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ensure focus is on an InputBox

Post by stuck »

Thanks, I thought a request for the code would be the immediate response. Sadly though my company does not allow me to upload my code.

I'm on Win 10 2004 and I'm using Word 365. The core of the Word VBA code is reaching out and opening an Excel 365 workbook, from which data is copied, pasted and then reformatted. That workbook is then closed and the code loops to do the same sort of thing on another workbook. First time round the loop, the inputbox that captures a number (of tables to reformat) has the focus but nextt ime round it does not.

A different Word macro opens a .html file, copies and pastes the entire contents (multiple tables) into a document and then reformats each of the tables. This code has an outer loop to step through a series of .html files and an inner loop to reformat each of the tables within each file. This inner loop suffers for the same problem, a dialog is presented that has the focus first time through but not thereafter.

If I add a pointless MsgBox immediately before where the inputbox/dialog is triggered, akin to:
if loopCounter <>1 then
msgbox("please click OK to continue")
end if
then the interaction required by that seems to bring the focus back from where ever it is and so the following line that presents the InputBox/a dialog box does have the focus and the rest of the code completes as expected.

So another way of asking my original question is how do I get the code to do whatever presenting a pointless msgbox does, which appears to be bring the focus from where ever it is back into the code?

Ken
Last edited by stuck on 03 Nov 2020, 16:54, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ensure focus is on an InputBox

Post by HansV »

It's a weird problem - the InputBox and MsgBox functions are basically invocations of the same Windows dialog, as far as I know. So I don't understand why MsgBox would force the focus, while InputBox won't always do that...
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ensure focus is on an InputBox

Post by stuck »

Agreed and in the absence of any code for close inspection I'm not expecting a miracle cure. I was just wondering if someone would be able to say, "Oh, yeh, seen that sort of thing, this might help..."

No matter, thanks anyway.

Ken

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: ensure focus is on an InputBox

Post by Doc.AElstein »

Hello ken,
I never saw this problem with an Input box. ( I use one macro , for example, every day , that has an input box in a search loop that keeps going and never stops looping until you add in specific data. Whether you loop once or a hundred times, the input box always has the focus)
I have had similar problem/ bugs with other pop up things. Somehow, VBA seems to trip over itself sometimes when doing pop up things and this causes it to somehow loose focus on what it is / was doing.
The temptation is to add wait statements to see if that helps. But that rarely does help

Two workarounds that I use in those situations, which sometimes work.

_1) Adding a couple of DoEvents in a few places
DoEvents: DoEvents

Sometimes it helps to have them coulpe of DoEvents before the thing that pops up and sometimes it helps to have them after.
If you want to try this, then it is best to put a lot of them in your macro to see if it helps. If it does, then you can take them out one at a time until you find the one that is curing the problem

_2) Break the macro up into macros starting one after the other.
In other words, if I have this originally …._

Code: Select all

 Sub OriginalmacroWithProblem()
' Do stuff 1
' The Input Box bit
' Do stuff 3
End Sub
_... then this would be the fix

Code: Select all

 Sub DoStuff1()
' Do stuff 1
Application.OnTime Earliesttime:=Now(), Procedure:="TheInputBoxBit"
End Sub
Sub TheInputBoxBit()
' The Input Box bit
Application.OnTime Earliesttime:=Now(), Procedure:="DoStuff3"
End Sub
Sub DoStuff3()
' Do stuff 3
End Sub

Note: On the occasions that this fix above has cured a problem / bug , then this alternative below has never cured that problem

Code: Select all

 Sub DoItWithCalls()
 Call Macro1
 Call MacroInputBoxStuff
 Call Macro3
End Sub
Sub Macro1()
' Do stuff 1
End Sub
Sub MacroInputBoxStuff()
' The Input Box bit
End Sub
Sub Macro3()
' Do stuff 3
End Sub
So it seems to be the stopping, and starting a fresh that cures the problem
Waiting, even for a large amount of time, rarely solves the problem.
_._________________________________________

_3)
One last thing/ shot in the dark:
You mention that you have a workaround involving bringing up a pointless msgbox
So another shot in the dark as a workaround, might be to call up the self cancelling message box. There is no interaction necessary, since it cancels itself, so you save yourself the trouble of having to click OK
You can set the cancel time to be quick, and perhaps experiment with that a bit.
As you are not interacting with it, it might not solve the problem. I don’t know. Its just another idea you could try.

The built in self cancelling message box has been broken for about 20 years, so, unless its been fixed recently, you can use as alternative API stuff to get it.
I put an example in the uploaded files.
You call it like this

Code: Select all

 Dim WndNumber As Long
 APIsinUserDLL_MsgBox hWnd:=WndNumber, Prompt:="Just here for a sec", Title:="NonModalPopUpThingy", uType:=4, wLanguageID:=0, Delay_ms:=1000

Code: Select all

 Sub TestSelfcancellingMsgBox()
' other stuff
' other other stuff
 
 Dim WndNumber As Long
 APIsinUserDLL_MsgBox hWnd:=WndNumber, Prompt:="Just here for a sec", Title:="NonModalPopUpThingy", uType:=4, wLanguageID:=0, Delay_ms:=1000 ' '  To make this work, you need at the top of a normal code module, (after any  Option Explicit  )  the following code line       Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long

' other other other stuff
End Sub
For that to work, you need at the top of any normal code module something like this

Code: Select all

 Option Explicit
Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long



Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ensure focus is on an InputBox

Post by stuck »

A much simpler way of dismissing the dummy MsgBox is:

Code: Select all

.
.
    SendKeys "%s{Enter}"
    MsgBox ("please just click OK")
.
.
NB I'm not claiming this is any better / more reliable than your efforts, just that it's worth a try. In my case it does the trick - at least today it does the trick, tomorrow who knows!

One other thing to note, using SendKeys bings other problems with it, notably it messes with the state of the NUMLOCK key. Persuading the NUMLOCK to be on when the macro completes is another can of worms. At the moment my weapon of choice is:

Code: Select all

.
.
.
    Dim wshshell As Object
    Set wshshell = CreateObject("WScript.Shell")
    wshshell.SendKeys "{NUMLOCK}"
    Set wshshell = Nothing
    
 End Sub
I say at the moment because until recently this alternative was working:

Code: Select all

Public Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
Public Const VK_NUMLOCK = &H90

.
.
.
    If GetKeyState(VK_NUMLOCK) <> 1 Then
        SendKeys "{Numlock}"
    End If

End Sub
It's tempting to sign off with 'have fun!' but this more of a :hairout: :hairout: situation.

Ken

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: ensure focus is on an InputBox

Post by Doc.AElstein »

stuck wrote:
05 Nov 2020, 11:21
A much simpler way of dismissing the dummy MsgBox is:

Code: Select all

.
    SendKeys "%s{Enter}"
    MsgBox ("please just click OK")
.
Hello ken,
Once again I have been surprised by what the send keys can do: A few times its use has surprised me, and added new possibilities …
Send keys seems to have a bad reputation amongst some Experts who say it can be unstable and a dangerous thing. I don’t have much experience with it myself. So far I have not had any problems, but have not used it much yet.
( Most recently I saw it can be used to make a drop down list associated with a cell drop down when selecting the cell, without selecting the arrow. I was most impressed by that. Useful, for example, if you have users that don't understand the arrow thing ( https://www.mrexcel.com/board/threads/a ... st-5550521 ) )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also