Home |
Search |
Today's Posts |
#1
|
|||
|
|||
clicker that advances one number per click in a cell
Hi,
I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#2
|
|||
|
|||
Hi!
Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#3
|
|||
|
|||
That's Great!
Thanks for your help!!!!!!!! Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#4
|
|||
|
|||
Thanks,
Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#5
|
|||
|
|||
Hi!
You'd need some VBA code for that. Can't help you with that, sorry. Maybe someone that can will chime in or post to the programming group. Biff "Rayco" wrote in message ... Thanks, Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#6
|
|||
|
|||
A little setup work first.
rightclick on that spinner and choose format control on the control tab Make the current value: 2 minimum value: 1 maximum value: 3 incremental change: 1 and remove the cell link Then paste this into a general module in the workbook's project: Option Explicit Sub IncrementActiveCell() Dim mySpinner As Spinner Set mySpinner = ActiveSheet.Spinners(Application.Caller) With ActiveCell If IsNumeric(.Value) Then .Value = .Value + mySpinner.Value - 2 Else Beep End If End With mySpinner.Value = 2 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Then back to excel and rightclick on the spinner. Choose Assign macro and assign IncrementActiveCell to the spinner. Rayco wrote: Thanks, Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco -- Dave Peterson |
#8
|
|||
|
|||
Thanks everyone for being so helpful.
Rayco "Dave Peterson" wrote in message ... A little setup work first. rightclick on that spinner and choose format control on the control tab Make the current value: 2 minimum value: 1 maximum value: 3 incremental change: 1 and remove the cell link Then paste this into a general module in the workbook's project: Option Explicit Sub IncrementActiveCell() Dim mySpinner As Spinner Set mySpinner = ActiveSheet.Spinners(Application.Caller) With ActiveCell If IsNumeric(.Value) Then .Value = .Value + mySpinner.Value - 2 Else Beep End If End With mySpinner.Value = 2 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Then back to excel and rightclick on the spinner. Choose Assign macro and assign IncrementActiveCell to the spinner. Rayco wrote: Thanks, Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco -- Dave Peterson |
#9
|
|||
|
|||
Great Don!!
adding Cancel = TRUE on line before End Sub goes ahead and "inputs" the number versus requiring any added clicks or enter key. Thanks, Jim "Don Guillett" wrote in message ... try putting this in the SHEET module. Modify to suit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$6" or target.address="$A$7" Then On Error GoTo fixit Application.EnableEvents = False If Target.Value = 0 Then oldvalue = 0 Target.Value = 1 * Target.Value + 1 oldvalue = Target.Value fixit: Application.EnableEvents = True End If End Sub -- Don Guillett SalesAid Software "Rayco" wrote in message ... Thanks, Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#10
|
|||
|
|||
glad to help
-- Don Guillett SalesAid Software "Jim May" wrote in message news:zaoJe.12646$MZ6.2337@lakeread01... Great Don!! adding Cancel = TRUE on line before End Sub goes ahead and "inputs" the number versus requiring any added clicks or enter key. Thanks, Jim "Don Guillett" wrote in message ... try putting this in the SHEET module. Modify to suit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$6" or target.address="$A$7" Then On Error GoTo fixit Application.EnableEvents = False If Target.Value = 0 Then oldvalue = 0 Target.Value = 1 * Target.Value + 1 oldvalue = Target.Value fixit: Application.EnableEvents = True End If End Sub -- Don Guillett SalesAid Software "Rayco" wrote in message ... Thanks, Can I take it one step further? I need to have six counters on one sheet. Instead of creating a form for each cell, can I somehow set it up so that it will only increment the cell that has the focus on it? Thanks, Rayco "Biff" wrote in message ... Hi! Excel has a "button" made just for this situation. It's called a spinner. Right click any toolbar and from the list, select Forms. From the toolbar that appears click on the spinner icon. It looks like an up arrow on top of a down arrow. Get it placed and sized how you want it then right click on it and select Format Control. Fill in the info. The cell link will be the cell that you want to increase (or decrease) when you click the arrows. Biff "Rayco" wrote in message ... Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco |
#11
|
|||
|
|||
I'm running into the same dilemma, but my users are having trouble pressing the up instead of the down button on the spinner. Is there a way to make this a button with the same increment functionality? Or is there a way to remove the bottom half of the spinner? Ideally, the button would fill the entire cell in my scenario, being as these users are entering the data on a tablet PC. -- Nathan77 ------------------------------------------------------------------------ Nathan77's Profile: http://www.excelforum.com/member.php...o&userid=26553 View this thread: http://www.excelforum.com/showthread...hreadid=393627 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
How do I sort a column a unique number? | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |