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

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

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

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

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

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

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

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

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

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

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