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.

 

[Joe4]

We need to know a little more regarding the details of how you would like this to work.

How do you determine which cell to update?
How to you envision communicating whether it is an increase or decrease, and how much?

 

[nmh7]

We need to know a little more regarding the details of how you would like this to work.

How do you determine which cell to update?
How to you envision communicating whether it is an increase or decrease, and how much?

I want it to increase or decrease by one. But only changing the value of the cell I click on. I have a list of a bunch of parts and when I use one I want to decrease the value in the cell that represents only that part.

 

[nmh7]

I added buttons but they are tied to only one cell. I don't want to have 100 buttons. i just want one to be able to control all the cells individually depending on which cell I have selected when clicking the button to increase or decrease.

 

[Joe4]

OK, you still have not indicated how to tell it whether you want to increase or decrease, so here is a procedure that will ask you wish one you want, then add or subtract one from the active cell:

Sub MyMacro()

    Dim x As String
   
    x = InputBox("Do you wish to (I)ncrease or (D)ecrease the value?")
   
    Select Case UCase(x)
        Case "I"
            ActiveCell = ActiveCell + 1
        Case "D"
            ActiveCell = ActiveCell - 1
        Case Else
            MsgBox "You did not make a valid selection.", vbOKOnly
    End Select
   
End Sub

So you can attach this code to your button.

 

[Eric W]

Here's a way to do it without any buttons. You would right click the cell you want, the context menu would have "Add 1" and "Subtract 1" on it, and you just select the one you want.

Easier way to create multiple macros

I am creating an inventory/stock control sheet for a client. They have 100-130 items they want to run a stick control on. Then once they have the starting numbers they want two buttons on each row, one to increase the Qty and the other to decrease the Qty by 1. So far I have created the first...

www.mrexcel.com

The lines in red restrict this functionality to specific sheets and/or ranges. Let us know if you want to adjust those.

 

[nmh7]

OK, you still have not indicated how to tell it whether you want to increase or decrease, so here is a procedure that will ask you wish one you want, then add or subtract one from the active cell:

Sub MyMacro()

    Dim x As String
  
    x = InputBox("Do you wish to (I)ncrease or (D)ecrease the value?")
  
    Select Case UCase(x)
        Case "I"
            ActiveCell = ActiveCell + 1
        Case "D"
            ActiveCell = ActiveCell - 1
        Case Else
            MsgBox "You did not make a valid selection.", vbOKOnly
    End Select
  
End Sub

So you can attach this code to your button.

I actually like this. Is there a way to ask how many to increase or decrease? in case for example I use a part 20 times, I don't want to have to run the macro 20 times

 

[nmh7]

Here's a way to do it without any buttons. You would right click the cell you want, the context menu would have "Add 1" and "Subtract 1" on it, and you just select the one you want.

Easier way to create multiple macros

I am creating an inventory/stock control sheet for a client. They have 100-130 items they want to run a stick control on. Then once they have the starting numbers they want two buttons on each row, one to increase the Qty and the other to decrease the Qty by 1. So far I have created the first...

www.mrexcel.com

The lines in red restrict this functionality to specific sheets and/or ranges. Let us know if you want to adjust those.

Is there a way where it asks how many I want to increase or decrease?

 

[Joe4]

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

 

[Eric W]

Is there a way where it asks how many I want to increase or decrease?

You could change the macros to:

Public Function Add1()
Dim x as Long
    x = InputBox("How much do you want to increase by?")
    ActiveCell = ActiveCell + x
End Function
Public Function Subtract1()
Dim x as Long
    x = InputBox("How much do you want to decrease by?")
    ActiveCell = ActiveCell - x
End Function

But it seems to me that this would be more time consuming than just typing the new number in the cell. It requires you to take your hand off the mouse. You could add a few more items to the Context menu, +5, -5, etc., and just use them more than once if you have to.