Spinners aren't cutting it
 

I have a worksheet that will be used for manual data entry with tablet
PCs. However, my users are complaining about the buttons being "too
small." Since they are only tallying order quantities, what I really
need is a single button that increments the cell by +1. While this can
be done easily enough, it requires code to be written for each button.
I need to be able to make hundreds of buttons very quickly and assign
each of them to their own cell. Here is what I have written that
inserts the spinners. I need a better solution though. Help.

Sub SpinnerBuilder()
'
' SpinnerBuilder Macro
' Macro recorded 8/16/2005 by Nathan
'

With ActiveCell.Select
Col = ActiveCell.Address
Xloc = ActiveCell.Top
Yloc = ActiveCell.Left
CelHeight = ActiveCell.Height
End With
'
ActiveSheet.Spinners.Add(Yloc, Xloc, 9, CelHeight).Select
With Selection
..Min = 0
..Max = 30000
..SmallChange = 1
..LinkedCell = Col
..Display3DShading = True
..PrintObject = False
End With
With ActiveCell.Select
Selection.ClearContents
End With
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub


--
Nathan77
------------------------------------------------------------------------
Nathan77's Profile: http://www.excelforum.com/member.php...o&userid=26553
View this thread: http://www.excelforum.com/showthread...hreadid=398165

Spinners aren't cutting it
 

Nathan,

What about using a BeforeDoubleClick Event to increment each cell when it is
double clicked rather than clicking button or spinner?

Mike

"Nathan77" wrote:


I have a worksheet that will be used for manual data entry with tablet
PCs. However, my users are complaining about the buttons being "too
small." Since they are only tallying order quantities, what I really
need is a single button that increments the cell by +1. While this can
be done easily enough, it requires code to be written for each button.
I need to be able to make hundreds of buttons very quickly and assign
each of them to their own cell. Here is what I have written that
inserts the spinners. I need a better solution though. Help.

Sub SpinnerBuilder()
'
' SpinnerBuilder Macro
' Macro recorded 8/16/2005 by Nathan
'

With ActiveCell.Select
Col = ActiveCell.Address
Xloc = ActiveCell.Top
Yloc = ActiveCell.Left
CelHeight = ActiveCell.Height
End With
'
ActiveSheet.Spinners.Add(Yloc, Xloc, 9, CelHeight).Select
With Selection
.Min = 0
.Max = 30000
.SmallChange = 1
.LinkedCell = Col
.Display3DShading = True
.PrintObject = False
End With
With ActiveCell.Select
Selection.ClearContents
End With
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub


--
Nathan77
------------------------------------------------------------------------
Nathan77's Profile: http://www.excelforum.com/member.php...o&userid=26553
View this thread: http://www.excelforum.com/showthread...hreadid=398165

Spinners aren't cutting it
 

That sounds like a possible solution. I can't say I know how to apply
doubleclick property to cells. Any chance you might be able to direc
me to a sample

--
Nathan7
-----------------------------------------------------------------------
Nathan77's Profile: http://www.excelforum.com/member.php...fo&userid=2655
View this thread: http://www.excelforum.com/showthread.php?threadid=39816

Spinners aren't cutting it
 

Hi Nathan,
Install by rightclick on sheettab then view code then insert the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
if lcase(target.value) = "ok" then
target.value = ""
else
target.value = "ok"
End Sub

More information on Event macros
http://www.mvps.org/dmcritchie/excel/event.htm

A doubleclick event is a much more positive action than merely
activating a cell.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Nathan77" wrote in message
...

That sounds like a possible solution. I can't say I know how to apply a
doubleclick property to cells. Any chance you might be able to direct
me to a sample?


--
Nathan77
------------------------------------------------------------------------
Nathan77's Profile: http://www.excelforum.com/member.php...o&userid=26553
View this thread: http://www.excelforum.com/showthread...hreadid=398165

Spinners aren't cutting it
 

Nathan,

Here is one case. In this example, note that I have a named range MyRange
equal to the range B1:B30. You can change this range to be the cells you
want to increment.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim MyRange As Range
Set MyRange = Range("B1:B30")
Set isect = Application.Intersect(Target, MyRange)
If isect Is Nothing Then
Else
If IsNumeric(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value + 1
End If
End Sub


Mike

"Nathan77" wrote:


That sounds like a possible solution. I can't say I know how to apply a
doubleclick property to cells. Any chance you might be able to direct
me to a sample?


--
Nathan77
------------------------------------------------------------------------
Nathan77's Profile: http://www.excelforum.com/member.php...o&userid=26553
View this thread: http://www.excelforum.com/showthread...hreadid=398165

Spinners aren't cutting it
 

Sorry that was not what I tested with -- was missing test for column B (col 2)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If target.row = 1 then exit sub '-- you should have column headings
If Target.Column < 2 Then Exit Sub
If LCase(Target.Value) = "ok" Then
Target.Value = ""
Else
Target.Value = "ok"
End If
End Sub

You could use Intersect as suggested in the other solution but I would not
suggest a range limited to the number of rows if it is not.