Add button to increase/decrease cell value

[Robisbald]

Hi,

I want to add a button either side of a cell to decrease/increase the value of the middle cell by 1. I know how to do this the long winded way by adding an individual button and targeting the specific cell, but going into the macro code to edit the target cell for will take ages, especially since I've got multiple columns/rows I want to do this too.

Breakdown of what I'm after: lets say I have 3 columns (A, B C),

Column A - Button to decrease value in B by 1
Column B - Value - affected by button in column A/C
Column C - Button to increase value in B by 1

I don't mind if this is a button or just on cell click within the spreadsheet

Hopefully there's an easy way to do this

Thanks

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script will run when you double click on a cell in column A Or C

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified  6/29/2022  8:29:00 AM  EDT
If Target.Column = 1 Then: Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
If Target.Column = 3 Then: Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
End Sub

 

[Robisbald]

Principle is good and could adjust the spreadsheet layout to work, wondering if it can be tweaked to target so many rows down. Obviously the below isn't correct as it doesn't work but should give you an idea what I'm aiming for so far

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Range = ("E4:E39") Then: Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
If Target.Range = ("G4:G39") Then: Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
If Target.Range = ("E42:E48") Then: Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
If Target.Range = ("G42:G48") Then: Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
If Target.Range = ("J4:J20") Then: Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
If Target.Range = ("L4:J20") Then: Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
End Sub

Thanks

 

[My Aswer Is This]

Your original post said Columns A B C
And did not specify how many rows.

Looking at your last post the row numbers change for every different column

So, to do this would require a lot more code and what you're asking for may not be all.

What is the reasoning behind how many rows?
And what columns?

 

[My Aswer Is This]

Any time we try to write Vba code it's a lot easier when we understand the ultimate goal.

Like why did you say Row 45 or row 29 or row 65
Is this the last row in that column with data?
If so then we just tell the script to go to the last row with data
We do not have to say 48 or 56 or 67

 

[Robisbald]

Your original post said Columns A B C
And did not specify how many rows.

Looking at your last post the row numbers change for every different column

So, to do this would require a lot more code and what you're asking for may not be all.

What is the reasoning behind how many rows?
And what columns?

You're right, apologies. Was being nieve thinking I'd get the exact solution with how I worded it, I'm very new to vba code so only know the very basics. In my mind it could be done by targetting a range of cells for each the +1 & - 1 button. And I could copy the code down and just change the range. Didn't realise you can do it by column numbers.

Reason behind is its all positioned to help fit on one sheet when printed, so there's 11 tables with different categories, 5 tables running across, and then the others will be underneath with different row start points.

Happy to mark your code as the solution and start a new post explaining the exact requirements, could also create a dummy spreadsheet to upload as an example (as the actual one has sensitive data)

Thanks

 

[My Aswer Is This]

Well, you do not need to mark as solution.
You said:
"Reason behind is its all positioned to help fit on one sheet when printed, so there's 11 tables with"

Are you sure you know what a Table is?

A Table is a Range of data on a Sheet.
And each Table has a given name or is just referred to as Listobjects(1) or Listobjects(2)

Maybe you can explain it one more time.

Earlier you only mentioned:

Breakdown of what I'm after: lets say I have 3 columns (A, B C),

Column A - Button to decrease value in B by 1
Column B - Value - affected by button in column A/C
Column C - Button to increase value in B by 1

 

[Robisbald]

I'm probably getting some terminology mixed up, table isn't the right word. Either way I've managed to get there in the end, using part of your code and looking at some guides. The below is perfect for what I need

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
      Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
      Cancel = True
    End If
    If Not Intersect(Target, Range("C1:C5")) Is Nothing Then
      Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
      Cancel = True
    End If

End Sub

Apologies for the confusion in all this, appreciate the help.

 

[My Aswer Is This]

I'm probably getting some terminology mixed up, table isn't the right word. Either way I've managed to get there in the end, using part of your code and looking at some guides. The below is perfect for what I need

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
      Target.Offset(, 1).Value = Target.Offset(, 1).Value - 1
      Cancel = True
    End If
    If Not Intersect(Target, Range("C1:C5")) Is Nothing Then
      Target.Offset(, -1).Value = Target.Offset(, -1).Value + 1
      Cancel = True
    End If

End Sub

Apologies for the confusion in all this, appreciate the help.

Glad you found an answer. Take care