Excel VBA - How to add a popup that takes input number and adds/subtracts to cell?

[firexcelsior]

Hello!

So I have a column in Excel titled "Number of Units" and I want a popup message to appear whenever someone presses on that column that asks the user to input a number. I want that number to be added to or subtracted from the current value in the cell (hence giving a the new value for the number of units.

Any idea? I know that I probably need to use VBA, but can't seem to know where to start..

Thanks :D

 

[bobsan42]

Try this - place it in the relevant Sheet code module in VBE:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const colUnits = "D"
    Static i
    Dim cval
    With Target
        If .Column <> Columns(colUnits).Column Then GoTo ep
        If .Cells.Count > 1 Then GoTo ep
        cval = .Value
        If Not IsNumeric(cval) Then GoTo ep
        i = Application.InputBox("Provide a positive number to ADD or negative to SUBTRACT from the Number of Units", "Type a number", i, , , , , 1)
        If i = False Then GoTo ep
        If IsNumeric(i) Then
            Application.EnableEvents = False
            .Value = cval + i
            Application.EnableEvents = True
        End If
    End With
ep:
    On Error Resume Next
    cval = Null
End Sub

 

[firexcelsior]

Thanks

Try this - place it in the relevant Sheet code module in VBE:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const colUnits = "D"
    Static i
    Dim cval
    With Target
        If .Column <> Columns(colUnits).Column Then GoTo ep
        If .Cells.Count > 1 Then GoTo ep
        cval = .Value
        If Not IsNumeric(cval) Then GoTo ep
        i = Application.InputBox("Provide a positive number to ADD or negative to SUBTRACT from the Number of Units", "Type a number", i, , , , , 1)
        If i = False Then GoTo ep
        If IsNumeric(i) Then
            Application.EnableEvents = False
            .Value = cval + i
            Application.EnableEvents = True
        End If
    End With
ep:
    On Error Resume Next
    cval = Null
End Sub

 

[firexcelsior]

Works great!

 

[bobsan42]