Increase decrease

[nmh7]

Hi!
I am currently trying to add an increase and decrease button to my excel sheet. I know how to add the button and link it to a certain cell. I was wondering if there is a way to only have one set of buttons for the entire sheet but only increase or decrease the value of the cell I select without changing the others.

 

[nmh7]

Try this version then:

Sub MyMacro()

    Dim x As String
    Dim y As Long
  
    On Error GoTo err_chk
    x = InputBox("Do you wish to (I)ncrease or (D)ecrease the value?")
    y = InputBox("How much do you wish to increase/decrease by?")
  
    Select Case UCase(x)
        Case "I"
            ActiveCell = ActiveCell + y
        Case "D"
            ActiveCell = ActiveCell - y
        Case Else
            MsgBox "You did not make a valid increase/decrease selection.", vbOKOnly
    End Select
  
    On Error GoTo 0
    Exit Sub
  
  
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid number", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
      
End Sub

Hi Joe I was wondering if there is a way to have two separate buttons where one is for increase and one is for decrease. And they ask how many you want to use or add based on the button clicked. I know this requires 2 macros but I think it will be easier to use.

 

[Joe4]

Hi Joe I was wondering if there is a way to have two separate buttons where one is for increase and one is for decrease. And they ask how many you want to use or add based on the button clicked.

Sure, you already have all the code you need in my post. Just remove what you don't need for each one (don't be afraid to try some of these things out for yourself, it is a great way to learn!).

Sub MyIncrease()

    Dim y As Long
   
    On Error GoTo err_chk
    y = InputBox("How much do you wish to increase by?")
   
    ActiveCell = ActiveCell + y
   
    On Error GoTo 0
    Exit Sub
   
   
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid number", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
       
End Sub

Sub MyDecrease()

    Dim y As Long
   
    On Error GoTo err_chk
    y = InputBox("How much do you wish to decrease by?")
   
    ActiveCell = ActiveCell - y
   
    On Error GoTo 0
    Exit Sub
   
   
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid number", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
       
End Sub

 

[nmh7]

Sure, you already have all the code you need in my post. Just remove what you don't need for each one (don't be afraid to try some of these things out for yourself, it is a great way to learn!).

Sub MyIncrease()

    Dim y As Long
  
    On Error GoTo err_chk
    y = InputBox("How much do you wish to increase by?")
  
    ActiveCell = ActiveCell + y
  
    On Error GoTo 0
    Exit Sub
  
  
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid number", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
      
End Sub

Sub MyDecrease()

    Dim y As Long
  
    On Error GoTo err_chk
    y = InputBox("How much do you wish to decrease by?")
  
    ActiveCell = ActiveCell - y
  
    On Error GoTo 0
    Exit Sub
  
  
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid number", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
      
End Sub

This is my last question I promise. Is there a way I can add to the code so It only selects from a certain column if I select the entire row. For example If I click on the row 7 I want only the value In column K7 to change while the rest of the row stays the same.

 

[Joe4]

If you change this line:

    ActiveCell = ActiveCell + y

to this:

    Cells(ActiveCell.Row, "K") = Cells(ActiveCell.Row, "K") + y

then it will always update column K of whatever row you are in.