Im just learning vba and this is probably so simple its embarassing.

I have 2 q's and any answer would be greatly appreciated.


I have a form, with a frame with 2 option boxes.

If the first option box is picked the macro will add 1 to the value in
the activecell in my worksheet

If the second box is selected, it will subtract 1

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Try something like


Private Sub OptionButton1_Click()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

Private Sub OptionButton2_Click()
ActiveCell.Value = ActiveCell.Value - 1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Xispo" wrote in message
...

Im just learning vba and this is probably so simple its embarassing.

I have 2 q's and any answer would be greatly appreciated.


I have a form, with a frame with 2 option boxes.

If the first option box is picked the macro will add 1 to the value in
the activecell in my worksheet

If the second box is selected, it will subtract 1

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

How do you generalise that to a large number of option buttons?

Say you had 20 option buttons, it would be much better to pick that up with
one routine that deals with any optionbutton being clicked. How do you do
that?

Thanks

Geoff



"Chip Pearson" wrote in message
...
Try something like


Private Sub OptionButton1_Click()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

Private Sub OptionButton2_Click()
ActiveCell.Value = ActiveCell.Value - 1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Xispo" wrote in message
...

Im just learning vba and this is probably so simple its embarassing.

I have 2 q's and any answer would be greatly appreciated.


I have a form, with a frame with 2 option boxes.

If the first option box is picked the macro will add 1 to the value in
the activecell in my worksheet

If the second box is selected, it will subtract 1

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

If your adding one and subtracting one, what would you do for other option
buttons?

However, if you want to handle the events for multiple controls with one
routine you can use the technique documented by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

The technique is generic and can be used with all of the msforms controls,
either on a userform or on a worksheet. You can not trap events which are
provided by the container (control on userform and oleobject on worksheet)
such as the exit event on a userform.

--
Regards,
Tom Ogilvy


"GB" wrote in message
...
How do you generalise that to a large number of option buttons?

Say you had 20 option buttons, it would be much better to pick that up
with
one routine that deals with any optionbutton being clicked. How do you do
that?

Thanks

Geoff



"Chip Pearson" wrote in message
...
Try something like


Private Sub OptionButton1_Click()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

Private Sub OptionButton2_Click()
ActiveCell.Value = ActiveCell.Value - 1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Xispo" wrote in message
...

Im just learning vba and this is probably so simple its embarassing.

I have 2 q's and any answer would be greatly appreciated.


I have a form, with a frame with 2 option boxes.

If the first option box is picked the macro will add 1 to the value in
the activecell in my worksheet

If the second box is selected, it will subtract 1

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

"Tom Ogilvy" wrote in message
...
If your adding one and subtracting one, what would you do for other option
buttons?

You might have them add other amounts to the active cell value:

Button1 = add 1
Button2 = add 2

and so on.

So if you could find the x of OptionButtonx then you could handle all the
code with just one line:
ActiveCell.Value = ActiveCell.Value + x

I'll have a look at John Walkenbach's tip, but it sounds a bit swanky, with
a class module.

Thanks

Geoff





However, if you want to handle the events for multiple controls with one
routine you can use the technique documented by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

The technique is generic and can be used with all of the msforms controls,
either on a userform or on a worksheet. You can not trap events which are
provided by the container (control on userform and oleobject on worksheet)
such as the exit event on a userform.

--
Regards,
Tom Ogilvy


"GB" wrote in message
...
How do you generalise that to a large number of option buttons?

Say you had 20 option buttons, it would be much better to pick that up
with
one routine that deals with any optionbutton being clicked. How do you
do
that?

Thanks

Geoff



"Chip Pearson" wrote in message
...
Try something like


Private Sub OptionButton1_Click()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

Private Sub OptionButton2_Click()
ActiveCell.Value = ActiveCell.Value - 1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Xispo" wrote in message
...

Im just learning vba and this is probably so simple its embarassing.

I have 2 q's and any answer would be greatly appreciated.


I have a form, with a frame with 2 option boxes.

If the first option box is picked the macro will add 1 to the value
in
the activecell in my worksheet

If the second box is selected, it will subtract 1

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/