Macro Calc

msb

New Member
Joined
Sep 27, 2002
Messages
1
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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Forum statistics

Threads
1,144,310
Messages
5,723,637
Members
422,506
Latest member
mdindas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top