Buttons to add and subtract from value

[Jazzu]

Hello!

I have a following problem that I don't know how I would fix without making like 50 macros.

I'm making a file that keeps track of everything in our store, including inventory. I had this idea to have a number of products in stock and left of the cell a button that would subtract, and to the right a button to add to it. I'll attach a screenshot so you can see exactly what I mean (the image is showing only inventory of 1 product category).

No idea how to make the buttons work without making a macro for each one though.

Any help is very much appreciated!

Kind regards

 

[Zyndstoff]

It would be easier to have the worksheet react to a doubleclick:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Set TGT = Target
  UserForm1.Show
  Application.SendKeys "{ESC}"
End Sub

to make it work, you need a "Userform1" with three buttons:


This is the button-code:

Private Sub CommandButton1_Click()
 TGT = TGT + 1
 UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
  UserForm1.Hide
End Sub

Private Sub CommandButton3_Click()
  TGT = TGT - 1
  UserForm1.Hide
End Sub

Finally, a module to declare TGT:

Public TGT As Range

As a result, when you doubleclick a cell, the userform will pop up and ask to add 1or substract 1 to the doubleclicked cell or cancel without operation.

 

[RoryA]

FYI, you should replace Application.SendKeys "{ESC}" with Cancel = True

 

[RoryA]

If you use Form buttons and position them within the row to which they relate, you'd only need two macros, one for up and one for down. The down one would just be something like:

Sub DownOne()
with Activesheet.shapes(application.caller).topleftcell.offset(, 1)
.value = .value - 1
end with
end sub

 

[Jazzu]

Wow! Thank you both! Double click is very nice, however, Rory's solution does exactly what I needed!

I really apprecite it!

Thank you again and have a nice day!

 

[Jazzu]

If you use Form buttons and position them within the row to which they relate, you'd only need two macros, one for up and one for down. The down one would just be something like:

Sub DownOne()
with Activesheet.shapes(application.caller).topleftcell.offset(, 1)
.value = .value - 1
end with
end sub

Hi!

This code works great, however, I have another question.

I set the data validation rule to allow only numbers above 0. It works if I write the number by hand, however, if I click the button, it goes below 0 without trouble. What can I do about that?

Thank you!

 

[Jazzu]

Hi!

This code works great, however, I have another question.

I set the data validation rule to allow only numbers above 0. It works if I write the number by hand, however, if I click the button, it goes below 0 without trouble. What can I do about that?

Thank you!

I just remembered something! It would be perfect if the cell would turn blank if I decrease the value below 0.

Thank you!!

 

[RoryA]

Change the code to use:

With ActiveSheet.Shapes(Application.Caller).TopLeftCell.Offset(, 1)
   If .Value > 0 Then
   .Value = .Value - 1
   Else
   .Value = vbNullString
   End If
End With

You might also want to look at just using a spinbutton linked to the cell.

 

[Jazzu]

If you use Form buttons and position them within the row to which they relate, you'd only need two macros, one for up and one for down. The down one would just be something like:

Sub DownOne()
with Activesheet.shapes(application.caller).topleftcell.offset(, 1)
.value = .value - 1
end with
end sub

Hi again!

Thank you, that solved everything I needed!

I really appreciate your help!