Input Box not stopping

[hmltnangel]

Hi folks,

Hopefully a quick one I had a MsgBox pop up with the following code.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("D8") > Range("G8") Then
        MsgBox "Budget Exceeded by " & Abs(Range("D8") - Range("G8"))
    End If

End Sub

This worked fine.

I need to change it a little so that its an input box with a field for a user to complete. The data they complete will then populate a cell. SO I changed the code to

rivate Sub Worksheet_Change(ByVal Target As Range)
    Dim myValue As Variant
    If Range("D8") > Range("G8") Then

        MsgBox "Budget Exceeded by " & Abs(Range("D8") - Range("G8"))
        myValue = InputBox("Explain why budget exceeded")
        Range("B10").Value = myValue
        
    End If

End Sub

WHich "works", but then doesnt stop after the user has input data.

Any suggestions for what im missing

 

[Jaafar Tribak]

Maybe this ;

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo RecoverEvents
    Dim myValue As Variant
    If Range("D8") > Range("G8") Then
        MsgBox "Budget Exceeded by " & Abs(Range("D8") - Range("G8"))
        myValue = InputBox("Explain why budget exceeded")
        Application.EnableEvents = False
        Range("B10").Value = myValue
    End If
RecoverEvents:
    Application.EnableEvents = True
End Sub

 

[hmltnangel]

Brilliant Jaafar - that works perfectly. Thanks for that.

 

[hmltnangel]

Quick change - is there a way to have the code run upon selection of the worksheet instead of when cell is changed?

 

[Joe4]

Use the "Worksheet_Activate" event instead, i.e.

Private Sub Worksheet_Activate()

    Dim myValue As Variant
        
    If Range("D8") > Range("G8") Then
        MsgBox "Budget Exceeded by " & Abs(Range("D8") - Range("G8"))
        myValue = InputBox("Explain why budget exceeded")
        Application.EnableEvents = False
        Range("B10").Value = myValue
        Application.EnableEvents = True
    End If

End Sub

 

[hmltnangel]

Brilliant, the subtle change in that is where I was going wrong. I couldnt get the worksheet activate code to work. Now that comes up perfect.

Is there a way to format the returned value part below so that it is to two decimal places? (ie - £25.96 or similiar?)

Abs(Range("D8") - Range("G8"))

 

[Joe4]

Brilliant, the subtle change in that is where I was going wrong. I couldnt get the worksheet activate code to work. Now that comes up perfect.

Is there a way to format the returned value part below so that it is to two decimal places? (ie - £25.96 or similiar?)

Abs(Range("D8") - Range("G8"))

No need to use VBA for that part.
Just pre-format cell B10 to be your desired format, right on the worksheet (go to cell B10, right-click on it, go to Format Cells, go the Number tab and select/enter your desired cell format.