Macro Calc

[msb]

I'm trying to create a command button that when clicked will reference a certain cell, check the value of that cell and add 1 to it and post the new value to that cell. Can you tell me how to do that?

 

[Yogi Anand]

On 2002-09-28 19:51, msb wrote:
I'm trying to create a command button that when clicked will reference a certain cell, check the value of that cell and add 1 to it and post the new value to that cell. Can you tell me how to do that?

Hi msb:

Welcome to the Board!

We can do it in two ways ...

1. Using Data Validation -- let us say our Workbook is Book1, our Worksheet is Sheet7, and our Cell of interest is C5.

Let us say, we currently have value 3 in cell C5. Then in an adjacent cell, say E5, let us write the formula =C5+1

Then select C5, then DATA|VALIDATION ... then Settings -- Allow|List -- =$E$5, and OK

Now when we select Cell C5, we will see a drop down arrow to its right, clicking on the drop down arrow, will bring up a value originally in cell C5 PLUS 1, ... Bingo, we select that, and we are done.

2. We can have a VBA solution ...

we create a command button in Sheet7 close to cell C5, and assign it the Macro Add1toAcell

in a Module of Book1,we insert the following code:

Sub Add1toAcell()
[Sheet7!C5]=[Sheet7!C5]+1
End Sub

now when we click on the command button, the value in cell C5 is increased by 1.

Regards!

Yogi

Edit: fixed Allow to Allow|List for Data Validation
This message was edited by Yogi Anand on 2002-09-29 09:23