+ Reply to Thread
Results 1 to 6 of 6

Spinners aren't cutting it

  1. #1
    Registered User
    Join Date
    08-23-2005
    Posts
    4

    Unhappy 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

  2. #2
    crazybass2
    Guest

    RE: 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
    >
    >


  3. #3
    Registered User
    Join Date
    08-23-2005
    Posts
    4

    Sounds useful

    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?

  4. #4
    David McRitchie
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >




  5. #5
    crazybass2
    Guest

    Re: 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
    >
    >


  6. #6
    David McRitchie
    Guest

    Re: 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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1