Subtraction using Excel VBA
Hi, I'm creating a sheet for the employees in my retail store. The spreadsheet has a column for the different items that I'm selling, and another column for the number of these items that I have in stock. Next to each of the item, I'd like to create a button.
So here is my question:
How do I assign a macro to the button such that it'll deduct 1 from the 'no. of stock' each time the button is clicked on?
Software/Hardware used:
Thank you in advance.
Software/Hardware used:
ASKED:
March 20, 2010 6:53 AM
UPDATED:
April 24, 2010 6:30 PM
Answer Wiki:
I have a few questions that need answering before a satisfactory solution can be offered. The reason for the question is also given to give you an idea why the question is asked.
1. Are more then 1 copy of the stock sheet going to be in use simultainiously in diferent locations? If there are more than 1 concurrent user, each with their own sheet open, then when each user clicks to deduct a quantity from the stock, it will have to query the stock item quantity to see if it is greater than zero, (in case someone has already allocated the stock in their open sheet) as it could create a minus stock value.
2. How are you going to control stock receipts? If you get stock in, at what stage is the stock going to be incremented, without corruption of the totals on open sheets. (Again query before deduction is indicated.)
3. Why not use Access instead of Excel? Access is a far better medium for this sort of thing. There are simple stock management databases already in the public domain, and it is not as memory hungry as Excel.
An Access db form can be created to do the stock deductions, and reading between the line you might later want to record who sold it, at what cost, and to which customer for commission purposes, or audit purposesetc.
To see all answers submitted to the Answer Wiki: View Answer History.
hi, thank you so much for your reply.
mine is a
hi, thank you so much for your reply. Mine’s a small shop where there will be only one employee in the store at a time.
The reason why I’m using an excel spreadsheet is so that each time an item sold is clicked on, this increases 1 in another sheet where I’m collecting past sales data on a weekly basis to do a trend analysis on that product. The trend analysis will be done based on 5 weeks. So when data from week 6 comes in, the trend will be done based on week 2-6 etc.
This trend analysis that allows me to know how much of that item’s inventory i need to have in store (due to the limited space).
I have 3 sheets
A) for employees to click on when item is sold. it also shows the number of each item that is in stock in the store, and what’s the critical level.
B) sales data sheet: linked to sheet A
C) a graph chart
Problem #1
For sheet A: What is the macro which will highlight a cell in red when it reaches the critical level (i.e time to bring in more stock)
Problem #2
How do I create a macro that is time-aware? For example, on the Monday of week 6, when an item is sold, this number will be under the column that says week6
Problem #3
How do I set the macro such that the graph from the trend analysis (sheet C) recalculates each week on Monday?
Basic picture of what it looks like:
Item/Wk 1 2 3 4 5 6
Item 293
Item 409
Firstly, sorry for taking so long to respond. For some reason, I didn’t get notified of an answer from my watch list.
Sounds like you have a good idea what you want to do. That sort of sheet could already be on the net, have you tried to search for freeware spreadsheets?
One important question was what version of excel are you using? The newer versions have access to many more solutions on the internet.
I have found this link
which is from microsofts web site. It may give you better insight to how its done.
If you need ideas for excel user forms, this site is useful
Another site is this one
They are very good examples with descriptions. You can copy and paste from them if you find the code useful. I hope these links are readable, I’ve never used the link function on here before.
I would seriously suggest using the form input method, as there is better control of what the user can do. If they have access to the whole sheet, they could overtype a cell in error and corrupt the data.
Problem 1.
The macro to change a font colour is quite easy to describe how its done, but the coding will need to be integrated into the stock debit button. Basically it is a comparison of the stock qty and then if it is = or less than the re-order trigger quantity, the ink colour is changed. This is better researched from VBA sites.
Problem 2.
In the form you could set a cell formatted to date format. As to it putting itself into a table for a particular week, I would have a cell on the form, which is set to the week number, and then sorts could use this for a key to sort on.
Problem 3.
Once again, I’d set a button to do this, but on an admin form. This form could be used to set the date/week number and any other macros that might be needed, but the staff will not see.
This is not the answer to how to write the sheet(s) needed, but hopefully you will get a better idea of what to think about and plan for, before the build of the sheet.
If you get any more queries, then post them and I’ll try my best to help.
I couldn’t have entered the links correctly, so I’ll do them again as lines of text. Copy the line and past it into you web browser.
This is the Microsoft Excel templates site. Specific to inventory forecasting using moving average.
http://office.microsoft.com/en-gb/excel/HA010864801033.aspx
Just as a topic of interest, might help you using templates.
http://www.ehow.co.uk/how_5066121_track-inventory-excel.html
This is a blog which is helpful from a problem solvers point of view.
http://www.mvps.org/dmcritchie/excel/excel.htm
And this site gives more examples of sheets for different purposes.
http://contextures.com/excelfiles.html
Good luck.