Option Buttons

[Saxman]

I have created the VBA code below and option buttons in a group, so that when a
button is clicked the value is recorded in cell A.

However, I would like to record the next event in cell A2 and possibly so on up to
A25. Is this possible, and am I able to clear the events afterwards, so that I
begin recording in cell A1 again? I don't want to end up with 25 sets of option
buttons on my worksheet!

Sub SelectC()
Range("A1").Value = "=15"
End Sub
Sub SelectD()
Range("A1").Value = "=15"
End Sub
Sub SelectCD()
Range("A1").Value = "=10"
End Sub

Thanks.
--

 

[Guest]

Use the following in your procedures to add new records.

If range("A1").value = "" then
Range("A1").value = 15
else
range("A1").end(xldown).offset(1,0).value = 15
end if

Add another button that clears the selections with code like...
if range("A1").value = "" then
exit sub
else
range(range("A1"),range("A1").end(xldown)).clearcontents
End if

 

[Saxman]

If range("A1").value = "" then
Range("A1").value = 15
else
range("A1").end(xldown).offset(1,0).value = 15
end if

Add another button that clears the selections with code like...
if range("A1").value = "" then
exit sub
else
range(range("A1"),range("A1").end(xldown)).clearcontents
End if

Do I paste the existing code onto the end of the above.

I have added another option button in the same group. What do I call it for it to
work in order to assign a macro to it?

TIA

--

 

[Saxman]

Do I paste the existing code onto the end of the above.

I have added another option button in the same group. What do I call it for it to
work in order to assign a macro to it?

TIA

Sorry, I meant to say, should I paste the above code onto the end of:-

Sub SelectC()
Range("A1").Value = "=15"
End Sub
Sub SelectD()
Range("A1").Value = "=15"
End Sub
Sub SelectCD()
Range("A1").Value = "=10"
End Sub

--

 

[Guest]

"Sub" is the beginning of a procedure and "End Sub" ends that procedure. You
should replace the line between the beginning and ending with the code I
provided. You'll have to change the number value based on the selection
criteria.
i.e. replace the line "Range("A1").Value = "=15"" with what I suggested.

What you have provided is actually something that the button refers to to
get the code to run when pressed. I'd need more information to know what you
need to do to get the new button to run.

 

[Saxman]

"Sub" is the beginning of a procedure and "End Sub" ends that procedure. You
should replace the line between the beginning and ending with the code I
provided. You'll have to change the number value based on the selection
criteria.
i.e. replace the line "Range("A1").Value = "=15"" with what I suggested.

What you have provided is actually something that the button refers to to
get the code to run when pressed. I'd need more information to know what you
need to do to get the new button to run.

In a nutshell, one of my interests is horseracing.

On the racecard it is noted whether a horse has won over the course (C), distance
(D) or course and distance (CD). I award point for the above. 10 pts for a (C) or
(D) and 22 pts for (CD).

Horses would be listed in column B, maybe up to a maximum of 25, depending on the
number of runners.

My option buttons would be labelled C, D, CD and reset. Obviously, horse 1 would be
contained in cell B1 and its points recorded in cell A1, if any. I would then go
onto horse 2 and would need its points recorded in cell B2. Thinking logically, one
would also require 'None' or 0 points option button within the group, so that the
input data would shift one cell further down the column until all horses were
entered?



--

 

[Guest]

I would actually not use VBA for what you explained. I would have the horses
listed in Column A, in column B I would type C, D, or CD and in column C I
would have the following formula:
=if(or(B1="C",B1="D"),10,if(B1="CD",20,0))

VBA is great stuff, but life is much easier when I don't try to reinvent the
wheel.

 

[Saxman]

I would actually not use VBA for what you explained. I would have the horses
listed in Column A, in column B I would type C, D, or CD and in column C I
would have the following formula:
=if(or(B1="C",B1="D"),10,if(B1="CD",20,0))

VBA is great stuff, but life is much easier when I don't try to reinvent the
wheel.

I will try that and let you know. Thanks!

--

 

[Guest]

the 20 in the formula should actually be 22 based on the information you had.

 

[Saxman]

I would actually not use VBA for what you explained. I would have the horses
listed in Column A, in column B I would type C, D, or CD and in column C I
would have the following formula:
=if(or(B1="C",B1="D"),10,if(B1="CD",20,0))

VBA is great stuff, but life is much easier when I don't try to reinvent the
wheel.

This works absolutely fine. Thanks so much for the feedback.



--