Fill in InputBox using VBA

[qwerty12345]

Hello,I am writing a macro that calls another macro (from another workbook). This second macro generates an InputBox that I want my first macro to fill in as it runs.I'm aware this is a stupid thing to do, as I could change hte macro that generates the InputBox to just accept the needed value as an argument from the calling macro. But I have a business need to not change it around - I need the macro I'm writing now to interface with the other macro in the other book as it currently is.Any tips on how to fill in the InputBox using VBA?Thanks!

[qwerty12345]

A bit more background - the macro that needs the input box filled in is in a workbook that implements a model and provides a user interface to run the model. I'm creating a macro to automate doing multiple runs of this model. As I mentioned, I don't have the ability to change around the macros comprising the model, so I want to mimic a human user running it a bunch of times. So the steps are (1) updating inputs using the human interface, (2) running the model, and (3) exporting the results. This is all easy stuff, with the exception of the one subtlety I asked about above - being able to programatically mimic a human user typing into an InputBox.

[SamT]

Public Sub WithInputBox(Optional RemoteInput As String)
Dim Var As String
'
'
'If RemoteInput = "" Then
Var = InputBox ' ' '
Else
Var = RemoteInput
End If
'
'
'
End Sub

[qwerty12345]

Thanks for the answer and sorry for my delay - have been away awhile.I'm not sure if this solves my problem (or I don't understand the solution if it does). To reformulate the problem: macro A calls macro B using application.run. Macro B then generates an input box that I want to be able to fill in by using a variable in macro A. So, the effect is to pass a parameter from macro A to macro B via the input box generated by macro B.As I said, I have a business need to not change macro B (which was written for a human user) - therefore I want to write macro A to mimic a human user running macro B multiple times, hence the need to programatically fill in the input box.Thanks again for the help!

[SamT]

If you cannot modify Macro B in any way, then you may have to call B and set an OnTime script to send a KeyPress string one or two seconds after calling B. :dunno:

My previous (Badly typo'ed) suggestion was to modify B thusly

Public Sub MacroB(Optional RemoteInput As String)
'Dim Var As String 'where Var represents the current variable that accepts the InputBoxes value.

'Replace InputBox line with
If RemoteInput = "" Then 'MacroB not called by MacroA
Var =InputBox
Else
Var = RemoteInput
End If
'
'Rest of Code
'
End Sub

The MacroA would call MacroB like this

Sub MacroA90
MacroB "SomeValue"

Note that this change does not in any way affect the normal human usage of the MacroB.

[mikerickson]

OnTime won't work. The clock will stop until the user dismisses the InputBox.

I see a Macro calling a Function (with InputBox).
A quick fix would be to pass the desired result as the default value for the Function that calls the InputBox.
Any approach would involve modifying the routine that calls the InputBox.

[Aflatoon]

You can do it without modifying the called routine using something like this:
Option Explicit


Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, _
ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long

Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long

Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _
(ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long

Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long

Private Declare Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" _
(ByVal hDlg As Long, ByVal nIDDlgItem As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Any) As Long

Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, _
ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long

Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
Private Const HC_ACTION = 0
Private Const EM_REPLACESEL = &HC2
Private Const IDOK = &H1&
Private Const BM_CLICK = &HF5&
Public Const EM_SETSEL = &HB1

Private hHook As Long
Private strInputText As String


Public Function NewProc(ByVal lngCode As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
' this is the callback set up by the hook
Dim RetVal
Dim strClassName As String
Dim lngBuffer As Long

If lngCode < HC_ACTION Then
NewProc = CallNextHookEx(hHook, lngCode, wParam, lParam)
Exit Function
End If

strClassName = String$(256, " ")
lngBuffer = 255

If lngCode = HCBT_ACTIVATE Then 'A window has been activated

RetVal = GetClassName(wParam, strClassName, lngBuffer)

If Left$(strClassName, RetVal) = "#32770" Then 'Class name of the Inputbox
' make sure all text selected
SendDlgItemMessage wParam, &H1324, EM_SETSEL, &H0, -1&
' send the replacement text
SendDlgItemMessage wParam, &H1324, EM_REPLACESEL, &H0, strInputText
' press OK
SendDlgItemMessage wParam, IDOK, BM_CLICK, 0, 0&

End If

End If

'This line will ensure that any other hooks that may be in place are
'called correctly.
CallNextHookEx hHook, lngCode, wParam, lParam

End Function

Sub testCall()
Dim lngModHwnd As Long
Dim lngThreadID As Long

lngThreadID = GetCurrentThreadId
lngModHwnd = GetModuleHandle(vbNullString)
strInputText = "here's some text"
hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

Call routineWithInput
UnhookWindowsHookEx hHook

End Sub
Sub routineWithInput()
Dim strResp As String
strResp = InputBox("Hello", "testing", "blah")
Debug.Print strResp
End Sub



Obviously it's a lot simpler if you can change the called routine. ;)