How do I get an InputBox to run from within a Macro?

Posted by Craig Snuffy Smith on December 28, 1999 7:48 PM

I run lots of decision making macros but this one has me stumped

basically if cell A1=X then run IputBox for new value..

I can get the input box to run but not when the macro is running

Posted by Chris on December 29, 1999 5:11 AM

Craig,

Use a WorksheetChange() procedure. This will run the procedure each time a change is made to the worksheet. You can have it check the value of A1 for a particular value and run the input box if necessary. Because it is a small procedure, you shouldn't notice any speed problems. See help for more info on running worksheet event procedures.

HTH,
Chris

Posted by Ivan Moala on December 30, 1999 5:37 AM

Craig
Try something like this;
Assuming the input to monitor is a value &
Cell to monitor is A1 then

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRg As Range

Set MyRg = Range("A1")
If MyRg.Value = 2 Then
'Disable events to stop recursion if Canceled input
Application.EnableEvents = False
MyRg.Value = Application.InputBox("Enter new value", "New Value", Type:=1)
'0 A Formula
'1 A Number
'2 Text (a string)
'4 A logical value (True or False)
'8 A cell reference, as a Range object
'16 An error value, such as #N/A
'64 An array of values
If MyRg.Value = False Then
MyRg.Value = 2 'Restore to Monitored value
End If
End If
Application.EnableEvents = True
End Sub

Ivan