Macro adds 1 to Cell in excel

  • Thread starter Thread starter Xispo
  • Start date Start date
X

Xispo

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
 
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
 
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 said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
Tom Ogilvy said:
If your adding one and subtracting one, what would you do for other option
buttons?
Click to expand...
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
 
Back
Top