Counting

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

I want to replace, with an Excel workbook, a blank hardcopy table in
which users count the number of occurrences of a particular kind (in
this case various types of queries at a reception desk) by marking
boxes with with a | for every occurence then a slash through every
|||| in the 'five bar gate' way. What's the best way to set up a
workbook so they can do something similar with one keystroke or one
mouse-click for each occurrence? I could just get them to increase
the number in each box by one every time they get a query, but I think
this might be confusing,
 
Hi rob

Not using the 5 bar gate method, but a simple addition when a cell is
clicked would be the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myrange As Range
Set myrange = Range("A1:A8") '<<-- Alter to suit
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, myrange) Is Nothing Then
Target.Value = Target.Value + 1
Range("B1").Activate ' to move focus away form the target range
End If
End Sub

To USE
Copy code above
Right click on the sheet tab you are using>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

I have chosen the range A1:A8 as the cells where the addition is to take
place. Amend to suit your case.
Equally, after clicking and the number being incremented in the cell, I am
moving the focus away for that cell, to ensure that the change selection
will get triggered next time the user wants to add another one.
I have used B1 as the cell to move to - again change to suit.
--
Regards
Roger Govier

robzrob said:
I want to replace, with an Excel workbook, a blank hardcopy table in
which users count the number of occurrences of a particular kind (in
this case various types of queries at a reception desk) by marking
boxes with with a | for every occurence then a slash through every
|||| in the 'five bar gate' way. What's the best way to set up a
workbook so they can do something similar with one keystroke or one
mouse-click for each occurrence? I could just get them to increase
the number in each box by one every time they get a query, but I think
this might be confusing,


__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi rob

Not using the 5 bar gate method, but a simple addition when a cell is
clicked would be the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myrange As Range
    Set myrange = Range("A1:A8")    '<<-- Alter to suit
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, myrange) Is Nothing Then
        Target.Value = Target.Value + 1
        Range("B1").Activate    ' to move focus away form thetarget range
    End If
End Sub

To USE
Copy code above
Right click on the sheet tab you are using>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

I have chosen the range A1:A8 as the cells where the addition is to take
place. Amend to suit your case.
Equally, after clicking and the number being incremented in the cell, I am
moving the focus away for that cell, to ensure that the change selection
will get triggered next time the user wants to add another one.
I have used B1 as the cell to move to - again change to suit.
--
Regards
Roger Govier











__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -
Click to expand...

Just what I needed. Thank you.
 
Back
Top