Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-131329%22%20slang%3D%22en-US%22%3EHow%20to%20add%2Fsubtract%201%20to%20a%20cell%20using%20a%20button%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-131329%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20set%20up%20a%20macro%20that%20either%20adds%20or%20subtracts%201%20from%20the%20value%20of%20a%20cell%20when%20I%20click%20on%20spin%20button.%20(Obviously%2C%20add%201%20when%20I%20click%20the%20up%20button%20and%20subtract%20when%20I%20click%20the%20down%20button)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-131329%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-138137%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%2Fsubtract%201%20to%20a%20cell%20using%20a%20button%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-138137%22%20slang%3D%22en-US%22%3E%3CP%3EChristopher%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAccording%20to%20below%20screenshot%2C%20you%20have%20two%20options%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ESpinButton%20(Form%20Control)%3C%2FLI%3E%0A%3CLI%3ESpinButton%20(ActiveX%20Control)%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3ETo%20find%20them%2C%20go%20to%20%3CSTRONG%3EDeveloper%3C%2FSTRONG%3ETab%20%26gt%3B%26gt%3B%20%3CSTRONG%3EControls%3C%2FSTRONG%3EGroup%20%26gt%3B%26gt%3B%20%3CSTRONG%3EInsert%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActiveX%20SpinButton%20is%20more%20flexible%2C%20it%20can%20come%20down%20to%20negative%20values.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20linked%20the%20first%20one%20which%20is%20SpinButton%20(Form%20Control)%20to%20cell%20E4%2C%20but%20%3CSPAN%20class%3D%22%22%3Ethe%20second%20is%20linked%20to%20the%20active%20cell.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25793iF8710A910B7C94E5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SpinButton%20Control.PNG%22%20title%3D%22SpinButton%20Control.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20attached%20the%20solution%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825803%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%2Fsubtract%201%20to%20a%20cell%20using%20a%20button%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825803%22%20slang%3D%22en-US%22%3EHaytham%20-%20thanks%20for%20the%20file.%20It%20took%20me%20about%20an%20hour%20to%20understand%20what%20was%20happening.%20Not%20being%20an%20expert%20I%20did%20not%20understand%20the%20difference%20between%20a%20%22Form%20Control%22%20and%20the%20%22ActiveX%20Control%22.%20The%20Form%20Control%20shows%20the%20%22Control%22%20tab%20while%20the%20ActiveX%20control%20requires%20the%20use%20of%20a%20macro.%20This%20will%20help%20me....%20PS%20one%20can%20use%20the%20%22Scroll%22%20Form%20Control%20to%20have%20the%20arrows%20left-right%20instead%20of%20up-down.%20You%20know%20this%20but%20may%20assist%20others...%20Dan%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825922%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%2Fsubtract%201%20to%20a%20cell%20using%20a%20button%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399305%22%20target%3D%22_blank%22%3E%40dcasciato%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20SpinButton%20(Form%20control)%20can%20only%20go%20up%20and%20down!%20This%20is%20one%20of%20its%20limitations!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20ActiveX%20version%20is%20more%20flexible%20as%20you%20can%20drag%20the%20sizing%20handles%20of%20the%20SpinButton%20to%20make%20it%20show%20up%20horizontally%20(Left%2FRight)%20or%20vertically%20(Up%2FDown)%20based%20of%20its%20height%20and%20width.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGo%20to%20Developer%20tab%20%26gt%3B%26gt%3B%20press%20Design%20Mode%2C%20then%20try%20to%20resize%20the%20SpinButton%20(ActiveX)%20in%20a%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Erectangular%3C%2FSPAN%3E%3C%2FSPAN%3Eshape%20and%20see%20how%20it's%20orientation%20will%20change%20to%20horizontally%20(Left%2FRight).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128871iFAE927168202F41D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SpinButton%20Orientation.png%22%20title%3D%22SpinButton%20Orientation.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Christopher Cole
Occasional Visitor

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 button)

3 Replies

Christopher,

 

According to below screenshot, you have two options:

  1. SpinButton (Form Control)
  2. 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.

 

SpinButton Control.PNG

 

I've attached the solution for you.

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

@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).

SpinButton Orientation.png

 

Regards