Button of 0 and 1

andrichara

New Member
Joined
Jan 13, 2019
Messages
14
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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
 
Upvote 0
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!)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
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