InputBox keeps prompting even after a value is entered

[excel27]

Greetings!

I am new to VBA so this might be a simple question.

I have an excel sheet where the cell D23 has a formula such that the integer value in D23 changes automatically once previous parameters are changed.

Anytime D23 value changes, an inputbox prompts for a value for cell D24. This value is entered and it duly appears in D24. However, the InputBox keeps popping up until you press 'Cancel'. Then it gives the following error: "Runtime error 13: Type mismatch".

The value entered in D24 via the inputbox is used in succeeding cells for further computation. Now I have noticed that the input box keeps reappearing only because the D24 value is called on in the next cells. If this value is not called on in any other cell then the InputBox does not loop.

Following is the code I have used:

Sub NewMu()
Dim NewMu1 As Integer
NewMu1 = InputBox("Insert New Mu w/ new self weight")
Range("D24").Value = NewMu1
End Sub

Private Sub Worksheet_Calculate()
Dim Xrg As Range
Set Xrg = Range("D23")
If Not Intersect(Xrg, Range("D23")) Is Nothing Then
NewMu
End If
End Sub

I would really appreciate if someone can propose a solution to ending this looping inputbox! Thanks!!

 

[Fluff]

Hi & welcome to the board.
How about

Sub NewMu()
Dim NewMu1 As Integer
Application.EnableEvents = False
NewMu1 = InputBox("Insert New Mu w/ new self weight")
Range("D24").Value = NewMu1
Application.EnableEvents = True
End Sub

 

[excel27]

THANKS!

Works like a charm!!

Can you explain the logic behind this or refer to some article? Only learning.

 

[Fluff]

Glad to help & thanks for the feedback.

setting EnableEvents=False prevents any event code (such as the Worksheet_Calculate event) from running.
But you must turn it back on which is what EnableEvents=True does.
Every time your sheet calculates you'll run NewMu, which in turn enters a value in D24, forcing the calculate event to fire again.

That said your Calculate event can be rewritten as

Private Sub Worksheet_Calculate()
NewMu
End Sub

Because you Set Xrg=D23 this line

If Not Intersect(Xrg, Range("D23")) Is Nothing

will always give true, so you can get rid of it.

 

[excel27]

Thank you for such a detailed reply. It really helps. And yes, you're right that extra code is useless - I've removed it now. Thank you for your help again.

 

[Fluff]

You're welcome