Results 1 to 5 of 5

Thread: Button of 0 and 1

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Button of 0 and 1

    hi guys i would like to make a 2 buttons.The one will result 0 and the other one 1 at A7:A47.so when i press the zero button for examplle will result a zero at A7,then when i press again any of both will result at A8..then A9 and so on.can you help me on that?thank you!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Button of 0 and 1

    Assuming that you have nothing in column A below row 47, here are the two prcoedures you need:
    Code:
    Sub ZeroButton()
    
        Dim r As Long
        
    '   Find next row to populate
        r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        
    '   Verify row selection
        If r < 7 Then
            r = 7
        Else
            If r > 47 Then
                MsgBox "Cell A47 already populated", vbOKOnly
                Exit Sub
            End If
        End If
        
    '   Populate cell
        Cells(r, "A") = 0
        
    End Sub
    
    
    Sub OneButton()
    
        Dim r As Long
        
    '   Find next row to populate
        r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        
    '   Verify row selection
        If r < 7 Then
            r = 7
        Else
            If r > 47 Then
                MsgBox "Cell A47 already populated", vbOKOnly
                Exit Sub
            End If
        End If
        
    '   Populate cell
        Cells(r, "A") = 1
        
    End Sub
    After adding the code, then just add a few command buttons and add this VBA code to each one.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Button of 0 and 1

    Another option:

    Code:
    Sub Button1()
      Call FillValue(1)
    End Sub
    Sub Button2()
      Call FillValue(0)
    End Sub
    Sub FillValue(n As Long)
      Dim i As Long
      For i = 7 To 47
        If Cells(i, "A").Value = "" Then
          Cells(i, "A").Value = n
          Exit For
        End If
      Next
      If i = 48 Then MsgBox "No cells available"
    End Sub
    Regards Dante Amor

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Button of 0 and 1

    Nice one Dante!

    I knew there had to be a way to avoid duplicating very similar code, and overlooked the obvious (a single procedure with parameters!)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Button of 0 and 1

    Quote Originally Posted by Joe4 View Post
    Nice one Dante!
    Thanks Joe
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •