Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Ron Ron is offline
external usenet poster
 
Posts: 250
Default a button to add 1 to a cell

I need to create a button that will ADD 1 to a cell everytime I click it.
Actually 3 bottons (1,5,10). I will need to repeat these buttons for 50
students to their total score. I don't know if this matters. I don't mind
creating buttons as long as they're easy to program.
Help Obe Wan
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: a button to add 1 to a cell

Hello Obe Wan,

To create a button that will add 1 to a cell every time you click it, you can follow these steps:
  1. Go to the Developer tab in the Excel ribbon. If you don't see the Developer tab, you can enable it by going to File Options Customize Ribbon and checking the box next to Developer.
  2. Click on the Insert button in the Controls group and select the Button control.
  3. Draw the button on the worksheet where you want it to appear.
  4. Right-click on the button and select "Assign Macro".
  5. In the Assign Macro dialog box, click on the "New" button to create a new macro.
  6. In the Visual Basic Editor, enter the following code:

    Formula:
    Sub AddOne()
        
    ActiveCell.Value ActiveCell.Value 1
    End Sub 
    This code will add 1 to the active cell whenever the button is clicked.
  7. Close the Visual Basic Editor and click "OK" in the Assign Macro dialog box.
  8. Test the button by clicking on it and verifying that the cell value increases by 1.

To create buttons for adding 5 and 10, you can follow the same steps but modify the code in step 6 to add 5 or 10 instead of 1. For example:

Formula:
Sub AddFive()
    
ActiveCell.Value ActiveCell.Value 5
End Sub

Sub AddTen
()
    
ActiveCell.Value ActiveCell.Value 10
End Sub 
To repeat these buttons for 50 students, you can copy and paste the buttons to each student's worksheet. You can also use the "Format Painter" tool to copy the formatting of the buttons to other cells.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default a button to add 1 to a cell


To keep from needing 150 macros, just use 3. Here are your macros, put
them into your sheet first.

Code:
--------------------
Sub Add1()
Selection.Value = Selection.Value + 1
Beep
End Sub

Sub Add5()
Selection.Value = Selection.Value + 5
Beep
End Sub

Sub Add10()
Selection.Value = Selection.Value + 10
Beep
End Sub

--------------------
Now turn on the Forms Toolbar, draw a button and select Add1 macro. Draw
another and select Add5, and the third uses Add10. Edit the text on the
buttons.

Now, the buttons will add the values noted to the currently selected
cell. If you don't like the "beeps" delete them, I thought the audible
confirmation the macro ran and the number was added was a good thing in
this situation.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49195

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Ron Ron is offline
external usenet poster
 
Posts: 250
Default a button to add 1 to a cell

I don't understand "put them into your sheet first"
I am using Access 2007 if that helps.
How do I "put" them?
Thanks again,
Ron

"JBeaucaire" wrote:


To keep from needing 150 macros, just use 3. Here are your macros, put
them into your sheet first.

Code:
--------------------
Sub Add1()
Selection.Value = Selection.Value + 1
Beep
End Sub

Sub Add5()
Selection.Value = Selection.Value + 5
Beep
End Sub

Sub Add10()
Selection.Value = Selection.Value + 10
Beep
End Sub

--------------------
Now turn on the Forms Toolbar, draw a button and select Add1 macro. Draw
another and select Add5, and the third uses Add10. Edit the text on the
buttons.

Now, the buttons will add the values noted to the currently selected
cell. If you don't like the "beeps" delete them, I thought the audible
confirmation the macro ran and the number was added was a good thing in
this situation.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49195


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,722
Default a button to add 1 to a cell

I believe JBeaucaire meant the VBA sheet first. However, you just mentioned
that you are using Access, and this is the Excel newsgroup. Are you in the
right forum? If you meant Excel...

From spreadsheet, right click on sheet name, choose "view code". Paste
JBeaucaire's code there.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ron" wrote:

I don't understand "put them into your sheet first"
I am using Access 2007 if that helps.
How do I "put" them?
Thanks again,
Ron

"JBeaucaire" wrote:


To keep from needing 150 macros, just use 3. Here are your macros, put
them into your sheet first.

Code:
--------------------
Sub Add1()
Selection.Value = Selection.Value + 1
Beep
End Sub

Sub Add5()
Selection.Value = Selection.Value + 5
Beep
End Sub

Sub Add10()
Selection.Value = Selection.Value + 10
Beep
End Sub

--------------------
Now turn on the Forms Toolbar, draw a button and select Add1 macro. Draw
another and select Add5, and the third uses Add10. Edit the text on the
buttons.

Now, the buttons will add the values noted to the currently selected
cell. If you don't like the "beeps" delete them, I thought the audible
confirmation the macro ran and the number was added was a good thing in
this situation.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49195




  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default I believe JBeaucaire meant the VBA sheet first.

Hello... This is almost exactly what I am trying to do, I have the "add 1" macro as discribed above working for one selected cell...

But I am trying to add 1 to the individual values of multiple cells when the "add 1" macro is ran. Can the code be modified to add 1 to the individual values of a predetermined group of cells? In my case it would be B2:B173...

Thank you!!!

On Monday, January 12, 2009 2:01 AM Ro wrote:


I need to create a button that will ADD 1 to a cell everytime I click it.
Actually 3 bottons (1,5,10). I will need to repeat these buttons for 50
students to their total score. I don't know if this matters. I don't mind
creating buttons as long as they're easy to program.
Help Obe Wan



On Monday, January 12, 2009 2:43 AM JBeaucaire wrote:


To keep from needing 150 macros, just use 3. Here are your macros, put
them into your sheet first.

Code:
--------------------
Sub Add1()
Selection.Value = Selection.Value + 1
Beep
End Sub

Sub Add5()
Selection.Value = Selection.Value + 5
Beep
End Sub

Sub Add10()
Selection.Value = Selection.Value + 10
Beep
End Sub

--------------------
Now turn on the Forms Toolbar, draw a button and select Add1 macro. Draw
another and select Add5, and the third uses Add10. Edit the text on the
buttons.

Now, the buttons will add the values noted to the currently selected
cell. If you don't like the "beeps" delete them, I thought the audible
confirmation the macro ran and the number was added was a good thing in
this situation.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49195



On Monday, January 12, 2009 8:48 AM Ro wrote:


I do not understand "put them into your sheet first"
I am using Access 2007 if that helps.
How do I "put" them?
Thanks again,
Ron

"JBeaucaire" wrote:



On Monday, January 12, 2009 3:25 PM Luke wrote:


I believe JBeaucaire meant the VBA sheet first. However, you just mentioned
that you are using Access, and this is the Excel newsgroup. Are you in the
right forum? If you meant Excel...

From spreadsheet, right click on sheet name, choose "view code". Paste
JBeaucaire's code there.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ron" wrote:




  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 86
Default I believe JBeaucaire meant the VBA sheet first.

Sub add_1_to_range()
Dim rng As Range, ocell As Range
Set rng = Range("B2:B173")
For Each ocell In rng
If ocell.Value < "" And Application.IsNumber(ocell.Value) Then
ocell.Value = ocell.Value + 1
End If
Next
End Sub


Gord Dibben Microsoft Excel MVP


On Fri, 19 Aug 2011 23:26:28 GMT, Lisa Cervantes
wrote:

Hello... This is almost exactly what I am trying to do, I have the "add 1" macro as discribed above working for one selected cell...

But I am trying to add 1 to the individual values of multiple cells when the "add 1" macro is ran. Can the code be modified to add 1 to the individual values of a predetermined group of cells? In my case it would be B2:B173...

Thank you!!!

On Monday, January 12, 2009 2:01 AM Ro wrote:


I need to create a button that will ADD 1 to a cell everytime I click it.
Actually 3 bottons (1,5,10). I will need to repeat these buttons for 50
students to their total score. I don't know if this matters. I don't mind
creating buttons as long as they're easy to program.
Help Obe Wan



On Monday, January 12, 2009 2:43 AM JBeaucaire wrote:


To keep from needing 150 macros, just use 3. Here are your macros, put
them into your sheet first.

Code:
--------------------
Sub Add1()
Selection.Value = Selection.Value + 1
Beep
End Sub

Sub Add5()
Selection.Value = Selection.Value + 5
Beep
End Sub

Sub Add10()
Selection.Value = Selection.Value + 10
Beep
End Sub

--------------------
Now turn on the Forms Toolbar, draw a button and select Add1 macro. Draw
another and select Add5, and the third uses Add10. Edit the text on the
buttons.

Now, the buttons will add the values noted to the currently selected
cell. If you don't like the "beeps" delete them, I thought the audible
confirmation the macro ran and the number was added was a good thing in
this situation.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49195



On Monday, January 12, 2009 8:48 AM Ro wrote:


I do not understand "put them into your sheet first"
I am using Access 2007 if that helps.
How do I "put" them?
Thanks again,
Ron

"JBeaucaire" wrote:



On Monday, January 12, 2009 3:25 PM Luke wrote:


I believe JBeaucaire meant the VBA sheet first. However, you just mentioned
that you are using Access, and this is the Excel newsgroup. Are you in the
right forum? If you meant Excel...

From spreadsheet, right click on sheet name, choose "view code". Paste
JBeaucaire's code there.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ron" wrote:



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer cell values to another cell by selecting button. Gryndar Excel Worksheet Functions 2 November 24th 08 02:21 AM
Cell working as a button? Cell color control Emerogork via OfficeKB.com Excel Worksheet Functions 1 August 30th 07 05:20 PM
Excel - create button to replace cell content with cell value blackmot Excel Worksheet Functions 3 December 7th 05 05:10 PM
Cell as Button [email protected] Excel Discussion (Misc queries) 3 August 29th 05 01:16 AM
Using a button in a cell DaveSunsation Excel Worksheet Functions 2 December 1st 04 04:02 AM


All times are GMT +1. The time now is 04:17 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"