button counter macro by row

stevelizelsa

New Member
Joined
Sep 25, 2015
Messages
5
Hello. So I am making a spreadsheet that is full of button counters (110 + and 110 -). I found a thread from 2010 that gives a pretty good code for making the button add or subtract from a specific cell

~Sub count()
Dim x As Integer
x = Range("A1").Value
Range("A1").Value = x + 1
End Sub

however what I am looking for is a similar macro that I can attach to each button that simply has the button add or subtract from the same row that the button is located but in column B. That way if I insert a new row in the middle the buttons still add or subtract from the correct cell immediately to the left i.e.

a b c d

1 + -
2 + -

with the above code if I were to insert a row between 1 and 2 the button in b2 would become b3 but would still add to cell d2 instead of d3

a single code that said add to cell column b row same as button and could attach it individually to all the buttons instead of new code for each button that would be fantastic
Not sure if this is possible as I know very little of coding but any help would be highly appreciated
Thanks
Steve
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
So are you saying you want a way to add 1 to every cell value in column "A" or to subtract a number from every value in column "A"

If that's what you want be can write one script to do this and you can remove all your two hundred buttons.
 
Upvote 0
Not exactly. I have 110 items that I am keeping track of my quantity of so say the item in row 35 I want to add 1 more to, I simply hit the + button in row 35 and 1 more should be added to say column b of row 35. However if I add a new item and it alphabetically would fall in between row 34 and 35 then I need to add a new row in that place, add the new item and put a new button in that row. However I don't want the old button that was previously in row 35 to continue to add to column b row 35, it now needs to add to column b row 36. However I don't want to have to manually go in and change all the code for now rows 36 and above to shift them all 1 spot. i.e.

A__________B____C_____D (sorry about all the lines, when I submit it reformats and is hard to get the idea
item1 ___________ + ____ -
item2 ___________ + ____ -
item3 ___________ +_____ -
item4 ___________ + _____ -

With the previous code stated I would have to associate button c1 with adding to b1, c2 adding to b2, c3 adding to b3 and so on. With this formula however if I added a row between 2 and 3, button c2 would become c3 but would still add to b2 instead of for the correct item in b3. I would then have to manually change each button to associate with the correct item again. What I would like is a macro that would keep the buttons in say row 4 to still associate with the correct item in row 4 and the correct count for item in row 4 even if that entire row became row 5
does that help?
 
Last edited:
Upvote 0
I thought of a better way to describe it but apparently I passed the edit time frame so ill just put it in a new post
A__________B____C_____D (sorry about all the lines, when I submit it reformats and is hard to get the idea)
item1 ___________ + ____ -
New row
item2 ___________ + ____ -
item3 ___________ +_____ -
item4 ___________ + _____ -
with the new row, button in column c next to item2 is now adding for the new row instead of adding for item2. When the new row is added I want the button in c, row item2, to continue adding in column b next to item 2. In other words button in c2 added to b2, when the new row is added c2 becomes c3 and should now add to b3 instead of b2 without me having to manually change the code
 
Upvote 0
This code will add two buttons to columns C:D, sized the same as the cells.
It will update the number in column B when the buttons are pressed.

Code:
Sub AddButton()


    Dim btn1 As Button
    Dim btn2 As Button
    Dim x As Long
    Dim rCell As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        .Buttons.Delete
        For x = 2 To 10
            Set rCell = .Cells(x, 3) 'Location of first button.
            Set btn1 = .Buttons.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height)
            With btn1
                .OnAction = "Add"
                .Caption = "Add 1"
                .Name = "BtnAdd" & x
            End With
            
            Set rCell = .Cells(x, 4) 'Location of second button.
            Set btn2 = .Buttons.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height)
            With btn2
                .OnAction = "Subtract"
                .Caption = "Subtract 1"
                .Name = "BtnSubtract" & x
            End With
            
        Next x
    End With


End Sub


Sub Add()


    Dim rCell As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rCell = .Range(.Buttons(Application.Caller).TopLeftCell.Address).Offset(, -1)
        rCell.Value = rCell.Value + 1
    End With


End Sub


Sub Subtract()


    Dim rCell As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rCell = .Range(.Buttons(Application.Caller).TopLeftCell.Address).Offset(, -2)
        rCell.Value = rCell.Value - 1
    End With


End Sub
 
Upvote 0
Steve. I would just like for you to give me one example of what your trying to do with out mentioning buttons.
There is absolutely no reason why you should need 220 buttons on one sheet.
Lets say it like this for example:
Range A1=A1+A2 Or maybe like:
Range A1=A1+B1

I don't understand terms like Item 1.
 
Upvote 0
I use this method all the time, That way you can have the same code for all you +1 buttons

another option:

this will add 1 to the value in column "B" that corresponds to the top left corner of the button.

Code:
Sub Do_it()
r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.row
Cells(r,"B")= cells(r,"B") +1
end sub

hth,



Steve instead of having all tehse button, a better option may be to setup up on macro that when you click in column A it will add +1 to the cell in column B of same row, if you click in columc C it could subtract one (-1) form B in came row.

Ross
 
Last edited:
Upvote 0
Thanks Darren for taking the time to post that for me. Next question however is how exactly do I insert that code into a particular row....all I know how to do is to create a macro and associate it with an image that I already placed?
 
Upvote 0
Here is the code to do it without any buttons

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C200")) Is Nothing Then _
      
r = ActiveCell.Row
c = ActiveCell.Column

If c = 1 Then x = 1
If c = 3 Then x = -1

Cells(r, "B") = Cells(r, "B") + x
End If
End Sub

rightclick on you worksheet then view code and pase it in
 
Upvote 0
The code will delete all the buttons on the sheet and recreate them - so if a new row is added this will just rearrange everything to fit. You can run the code by selecting the 'View Macros' button and executing the 'AddButton' macro.

You'll need to insert it into a regular code module rather than 'row':

http://www.contextures.com/xlvba01.html#Regular

  1. Copy the sample code that you want to use
  2. Open the workbook in which you want to add the code
  3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
  4. Choose Insert | Module
  5. Where the cursor is flashing, choose Edit | Paste

You'll need to update the code to work for you:

  • Near the top is a line of code With ThisWorkbook.Worksheets("Sheet1") - this is the worksheet name containing the buttons.
  • Two lines further down is this line of code For x = 2 To 10 . These are the rows that the buttons will be added to - you can either hard code these numbers or use a function to look up the last row containing data on your sheet.
  • The next line down is Set rCell = .Cells(x, 3) and there's a similar command a few lines further down - these are where the buttons will appear. It's row x, column 3 (or C) and the other line is row x, column 4 (or D). Update the column number to where you want the buttons.
  • There are two subs at the bottom of the code called Add and Subtract. In each sub is a command stating .Offset(, -1) and .Offset(, -2) . These are pinpointing the cells that need updating - -1 is 1 cell to the left of the button, -2 is two cells to the left of the button.

As a side note - if you put the word PUBLIC before the AddButton macro, and PRIVATE before the Add and Subtract macros it will only show the public macro in the 'View Macros' dialog.

As another side note - 'My Answer Is This' has a point in that you probably don't need all those buttons.

Hope that explains it. :)
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top