2 macros (click a button) to insert certain text.

[realest25]

I need 2 macros, one for a button when clicked to insert the text "NO", a second macro for a button when clicked to insert the text "YES". Both in range "B7:B200".

Meaning if I click the button "NO", it will insert the text "NO" in first cell from range "B7:B200", meaning B7. After that if I click the button "YES", it will insert the text "YES" in the next cell meaning B8. And so on, each time I click the button YES or NO, to insert the text "Yes" or "NO", in each cell one by one, in same column, range "B7:B200".

Example (please see below excel):
Step 1. Click button YES
Step 2. Click button NO
Step 3. Click button NO
Step 4. Click button YES
Step 5. Click button NO

etc

Test.xlsx
	A	B	C
1
2		NO
3
4		YES
5
6
7		YES
8		NO
9		NO
10		YES
11		NO
12
13
14
15
16
17
18
19
20
Sheet1

 

[Micron]

You could do this with 2 subs but if gets more complicated (e.g. font size/effect, cell colour etc.) then all of that gets repeated for each button. Otherwise it all goes into the sub that the buttons call so that it's only written once. YesButton_Click and NoButton_Click would be the names of YOUR button click events.

Sub YesButton_Click()
Application.EnableEvents = False 'optional in both events
InsertYN "YES"
Application.EnableEvents = True
End Sub

Sub NoButton_Click()
Application.EnableEvents = False
InsertYN "NO"
Application.EnableEvents = True
End Sub

Sub InsertYN(strYN As String)
Dim lRow As Integer

lRow = Cells(Rows.count, "B").End(xlUp).Row
Range("B" & lRow + 1) = strYN

End Sub

 

[My Aswer Is This]

Try this:
Put the script in the proper button:

Sub Yes()
'Modified 11/2/2022  10:31:35 PM  EST
Application.ScreenUpdating = False
Dim i As Long

    For i = 7 To 200
        If Cells(i, 2).Value = "" Then Cells(i, 2).Value = "Yes": Exit Sub
    Next
Application.ScreenUpdating = True
End Sub

Sub No()
'Modified 11/2/2022  10:31:35 PM  EST
Application.ScreenUpdating = False
Dim i As Long

    For i = 7 To 200
        If Cells(i, 2).Value = "" Then Cells(i, 2).Value = "No": Exit Sub
    Next

Application.ScreenUpdating = True
End Sub

 

[Micron]

Even shorter than what I posted (as I said, duplication can be omitted by calling another sub).

Sub YesButton_Click()
InsertYN "YES"
End Sub

Sub NoButton_Click()
InsertYN "NO"
End Sub

Sub InsertYN(strYN As String)
Dim lRow As Integer

Application.EnableEvents = False
lRow = Cells(Rows.count, "B").End(xlUp).Row
Range("B" & lRow + 1) = strYN
Application.EnableEvents = True

End Sub

I guess I missed that 'cause I'm trying to watch the ball game too.

 

[realest25]

Hello. Thank you both very much for your help and solutions. Mr Micron and Mr My Aswer Is This, both your replies are doing exactly what I need. How can I mark as solved if both your solutions are great for my needs ? ..

 

[My Aswer Is This]

Hello. Thank you both very much for your help and solutions. Mr Micron and Mr My Aswer Is This, both your replies are doing exactly what I need. How can I mark as solved if both your solutions are great for my needs ? ..

Just Mark one and that will be good. Glad to know we were able to help you

 

[Micron]

Thanks.
Hard to decide sometimes I guess but I'd say go by the one you use. If 2 or more were exactly the same solution, then first in?
What I like about calling another sub is that should you decide that the code needs to increase font size, bold it, center it, etc. then you only have to write all of that once. Using 2 subs to set the cell value, you'd have to write it twice. In your case, the only difference for the operation is yes or no, and since you need two subs because you have 2 buttons, might as well pass the decision to another sub? That's how I tend to do things.