adding/subtracting inventory between the sheets

Hi,

I've got this here inventory worksheet that needs a touch of VBA magic. Sheet one contains a list of all items in the inventory. For everyone but administrators, it's just for looking. On sheet two, sales and purchases are noted. Anyone can use this sheet. After a sale or purchase is input, the name of the user is selected and the row is date-stamped and locked.

What I'd like to do is make it so sales (ventas) and purchases (compras) entered on sheet 2 appropriately alter the number of articles in stock listed on sheet 1. For example, Jorge buys 4 items denoted as "LOCK Chapa 21LBB", records it on sheet 2, and the number of items in stock on Sheet 1 goes up by 4.

I used Excel 2007 to write the file but will install it onto a machine with Excel 2003.

Inventario.xls

Thanks kindly for any help you can provide!

Bonus functions:

1) Inventory list can be added to. Upon closing the file, the list automatically re-alphabetizes itself.

2) A column is added to sheet one in which the administrator can specify a restock number -- that is, the number at which it's time to start thinking about buying more of the item. When an inventory item reaches that number, the row goes yellow. When the inventory for the item reaches 0, the row goes red.

This is awesome, and thank you. I only ran into a couple of small snags.

The first was that after realphabetizing, the highlighted rows are broken up in an unsightly way. That was easily fixed by specifying a larger range of columns to apply the reordering to.

The more important one, which I don't know how to rix, is that even though the script I included is also in your script, I can't get the rows to lock after the names are entered on Worksheet 2. That's an important function because the users will wipe out all the data if the workbook doesn't block them from doing so.

Here's my file with your code pasted in there so you can see what I'm talking about:

Inventario + stnkynts.xls

One other question. Someone asked me about the possibility of attaching a password to each name so that the digital signature and datestamp couldn't be added without it. I'm not sure if it's a good idea, but is it doable? And what would be involved?

Originally Posted by non-pro

The more important one, which I don't know how to rix, is that even though the script I included is also in your script, I can't get the rows to lock after the names are entered on Worksheet 2. That's an important function because the users will wipe out all the data if the workbook doesn't block them from doing so.

Yup, thats because I disabled it because it was annoying me. Just remove the single quotation before this line. Note: The single quote is a quick way to disable a line of code.

Please Login or Register  to view this content.

One other question. Someone asked me about the possibility of attaching a password to each name so that the digital signature and datestamp couldn't be added without it. I'm not sure if it's a good idea, but is it doable? And what would be involved?

Doable yes, but probably beyond your skill. Assuming that each person access the database under their own separate windows logon you would encode using "Environ("Username")" to check to make sure that the name that they select in column D matches the approved Environ("Username"). So something like this within the big code:

Please Login or Register  to view this content.

Thanks for your very thorough answer. I'm going to play with that passwords thing next week, but I won't bother you about it if I can't figure it out. You're right that it's beyond my skill. I know very little VBA. I need to develop the skill in a systematic way so that I can start contributing to this forum, which has been very good to me.

I've run into a couple of problems that I can't manage to work out.

The original code I provided -- which you had to modify a bit -- no longer works as it should. When it's functioning correctly, entering a name in column D results in a timestamp in column E and then locks that row to further editing. As it's functioning now, the row is timestamped but the entire sheet then gets locked to editing, and the password ("Secret") is required to unlock it.

Also, I think I see that you added some error messages to ensure that the entries are done in the proper order. I can't get those to appear. When I try to make an entry in Column C before I do anything to Columns A or B, I get a runtime error that dumps me into the debugger. This is using Excel 2007. Under normal conditions it wouldn't be a big deal, but the people who'll be using the spreadsheet literally don't even know how to turn on the computer, so it needs to be as close to idiot-proof as possible.

Thank you again for your time and attention. I hope that when you're not on this board you're able to profit handsomely from these skills.

The whole problem is the password protecting. do yourself a favor and remove it or do it correctly

I don't know how to do it correctly. That's why I came here for help. But thank you for the time that you did put in.