Finding tricks to make a Standard InputBox/MsgBox Modeless.

[Jaafar Tribak]

By modeless , I mean two things:

1-Allowing to work with excel while the InputBox/Msgbox are displayed and
2-Allowing execution of subsequent code

In other words just like a Modeless UserForm.

Any ideas are welcome.

 

[Jaafar Tribak]

Following is an example of a Standard InputBox that allows a cell to flash while the InputBox is on display.

The actual code runs inside an API timer procedure which runs asynchroneously.

I have wrapped the modeless InputBox in the function: AsynInputBox.
This custom AsynInputBox function has the same signature as that of the standard vba InputBox for easy use.

Plug this into a standard module , run the Test Macro and see the flashing of Cell A1:

Option Explicit

Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal uIDEvent As Long) As Long


Private Function AsynInputBox _
(Prompt As String, _
Optional Title As String, _
Optional Default As String, _
Optional XPos As Variant, _
Optional YPos As Variant, _
Optional HelpFile As String, _
Optional Context As Long) As String

    SetTimer Application.hwnd, 0, 1000, _
    AddressOf AsynChroneousProcedure
    
    AsynInputBox = InputBox _
    (Prompt, Title, Default, XPos, YPos, HelpFile, Context)
    
    KillTimer Application.hwnd, 0

End Function


Private Sub AsynChroneousProcedure( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long)

    With Range("a1").Interior
        If Int(lpTimerFunc / 1000) Mod 2 = 0 Then
            .ColorIndex = 3 'red
        Else
            .ColorIndex = xlColorIndexNone
        End If
    End With

End Sub

Sub Test()

    Range("a1") = AsynInputBox("Enter a value in the flashing Cell  'A1'", _
    "AsynChronous Standard InputBox Demo.")
    
    Range("a1").Interior.ColorIndex = xlColorIndexNone
    
End Sub

the code inside the AsynChroneousProcedure Procedure could be changed to do anything not just flashing a cell. (carefull editing is required though as any unhadled error could crash the application !)

Now,if only we could make it possible to work with the worksheet while the InputBox is displayed it would be great.