Forum Discussion

Copper Contributor

Nov 26, 2017

How to add/subtract 1 to a cell using a button?

I'm trying to set up a macro that either adds or subtracts 1 from the value of a cell when I click on spin button. (Obviously, add 1 when I click the up button and subtract when I click the down butt...

Macros & VBA

Need Help

Haytham Amairah

Silver Contributor

Dec 18, 2017

Christopher,

According to below screenshot, you have two options:

SpinButton (Form Control)
SpinButton (ActiveX Control)

To find them, go to Developer Tab >> Controls Group >> Insert.

ActiveX SpinButton is more flexible, it can come down to negative values.

I linked the first one which is SpinButton (Form Control) to cell E4, but the second is linked to the active cell.

I've attached the solution for you.

SpinButton Example.xls37 KB

dcasciato

Copper Contributor

Aug 27, 2019

Haytham - thanks for the file. It took me about an hour to understand what was happening. Not being an expert I did not understand the difference between a "Form Control" and the "ActiveX Control". The Form Control shows the "Control" tab while the ActiveX control requires the use of a macro. This will help me.... PS one can use the "Scroll" Form Control to have the arrows left-right instead of up-down. You know this but may assist others... Dan

Haytham Amairah
Silver Contributor
Aug 28, 2019
dcasciato

Hi,

The SpinButton (Form control) can only go up and down! This is one of its limitations!

But the ActiveX version is more flexible as you can drag the sizing handles of the SpinButton to make it show up horizontally (Left/Right) or vertically (Up/Down) based of its height and width.

Go to Developer tab >> press Design Mode, then try to resize the SpinButton (ActiveX) in a rectangular shape and see how it's orientation will change to horizontally (Left/Right).

Regards