Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: button counter macro by row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default button counter macro by row

    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 by stevelizelsa; Sep 25th, 2015 at 10:17 AM.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,813
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: button counter macro by row

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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 by stevelizelsa; Sep 25th, 2015 at 11:07 AM.

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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

  5. #5
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,813
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: button counter macro by row

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    1,045
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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 by rpaulson; Sep 25th, 2015 at 11:46 AM.

  8. #8
    New Member
    Join Date
    Sep 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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?

  9. #9
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    1,045
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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

  10. #10
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button counter macro by row

    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.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •