Forum Discussion
Including counter (tally) on a spread sheeet
I have to do a stocktake of greeting cards for a charity shop. The cards are in sections - birthdays, weddings, condolence, congratulations, etc. Each card has a small price sticker on the back. The aim is to get the total value of the stock.
In the past somebody has used a sheet and listed all the prices and then added them up. There are about 2000 cards with prices in 5p increments between £1.20 and £2.50 - so there are 28 categories altogether. I want to have a spreadsheet with 28 or so rows for the prices and 2 columns - one for number of cards at a certain price and a second with total vale of the cards at that price.
To do this I want a counter so that I can just click on a cell (number of cards vs price) and it autmatically add one to the number of cards at a certain price. IT seems simple enough but I cannot find out how to do this using Excel, even though I tried using a YouTube instruction video and opened up Developer.
All help welcome. Thank you, Keith (Oxford, UK)
There are multiple ways to increment cell values, but "click[ing] on a cell" is not the best nor easiest way to do so.
Option 1: VBA (only)
Ahh, no. VBA allows the worksheet to take specific actions (such as incrementing a count) when certain events occur, but a Click event is not one of the built-in events for a Worksheet. So I'm going to oversimplify and say "it can't be done". A double-click event (BeforeDoubleClick) is available, however, so if you are willing to double-click instead of click the cells, you might pursue this.
Option 2: VBA and (form) buttons
From the Developer menu, you can place a (form) button somewhere on each category row, and write simple VBA code for each to increment the count for its related row.
To increment a count, you would then click the button. (I named my button "cmdIncrement01"; it is larger than necessary.)
A significant downside to each of the above options is that it involves VBA. To store VBA in a workbook (in a manner where it can be executed), you have to store the workbook as a macro-enabled workbook. With such a workbook, it is easy for anyone who can edit the workbook to insert malware, from just annoying to very damaging.Option 3: spin controls
From the Developer menu, you can place a spin control somewhere on each category row, and set properties for each and possibly for their related cells with counts. Right click each and select "Format Control…" to specify the linked cell with a count.To increment a count, you would then click the upper half of a spin control. (In my example, I placed the spin control on the right side of the cell, making it large, and changed the cell alignment to "Right (Indent)" with an Indent Level of 3, but that size and positioning is your choice.)
- Many thanks SnowMan55 for taking the trouble and time to respond, I confess that your instructions are a bit beyond me and I have reverted to using a table with prices and then using the ancient tally system - 4 vertical strokes with a horizontal line across them for the fifth. I'm just surprised that there isn't an app already on Excel. Regards, Keith.
