PDA

View Full Version : Help with user forms add and subtract



CHAD74
12-05-2005, 10:55 PM
I am pretty new to VBA and need some help. I need to make a form with combo box's data entry that I can add and and subtract items added or taken away. PLEASE SEE USRFORM1.

Rembo
12-06-2005, 06:04 AM
Hello Chad,


I am pretty new to VBA and need some help. I need to make a form with combo box's data entry that I can add and and subtract items added or taken away. PLEASE SEE USRFORM1.

You didn't describe what you want the form to do so I had to guess a little. I created some code to write/read values from your worksheet depending on the value selected (or newly typed in) in your ComboBox1. I also added some code to read the date set on the calendar. See the attachment.

I wonder though, wouldn't it be easier if the form, by default, read in the values from your worksheet for a given value in ComboBox1? Now you have to hit the Read button everytime.

Cheers,

Rembo

CHAD74
12-06-2005, 10:13 AM
My fault I should have left more details here is the original spreadsheet that I use. What I want to do is automate it so that it is dummy proof. We send and receive different peices of equipment and track them daily on a spread sheet. I want to use a user form to that when you select the item it sends it to the crew tracks it but then subtracts it from the crews total or if we receive it logs it in the flow sheet and then adds it to our inventory. Also is it possible to show the pool items inventory on a user form. I want to eliminate anyone being able to tamper with the spread sheet.

Also is it possible to have are daily count sheet on one of those tabs?

Inventory is are grand total
Flow sheet tracks equipment movement
Daily count tracks what we have daily.

Really apreciate your help I have been trying to learn the code myself by using found stuff on the net I have learned lots but not near enough to acomplish this task.:friends:

CHAD74
12-06-2005, 10:41 AM
I am pretty new to VBA and need some help. I need to make a form with combo box's data entry that I can add and and subtract items added or taken away. PLEASE SEE USRFORM1.
THANKS REMO
Book 2 is almost exactly what I was trying to do only the receive needs to add the users amount to the item inventory.

Rembo
12-07-2005, 02:05 PM
Hi Chad,

I looked at your workbook below and read your comment but I'm still a bit confused. Let me ask you some questions so I can get a clearer picture of it and create some useful code for you.

From what I understand of it is that you rent equipment to others or at least you place it there. You want to use an excel workbook to keep track of what you have and call that 'inventory'. Basically that is what you own, no matter where it is located. Is that correct?

You also want to keep track of what you rented out or where you placed it.The worksheet 'Flowsheet' shows this is that correct? If so, I'm missing some items on the Flowsheet. Why is that? If not, what exactly does the flowsheet represent?



We send and receive different peices of equipment and track them daily on a spread sheet.

How exactly do you 'track' what you send and received? Or better yet, if you send of some equipment where do you want to write that down in your workbook. And if you receive some of your equipment back, where you do you want to write that down in your workbook?


I want to use a user form to that when you select the item it sends it to the crew tracks it but then subtracts it from the crews total ..

What is the crews total?


.. or if we receive it logs it in the flow sheet and then adds it to our inventory.

Obviously there is a direct relation between the numbers in the three worksheets. It's important to get the exact relation on the table before writing the code.

If you use worksheet formula's, what happens with the numbers in your flowsheet if you purchase new tools and add to your inventory? As far as I can tell you didn't define a relation between time and inventory. Maybe it's not important for your situation, I don't know, but I only want to make it clear that these things have to be taken into consideration before creating some VBA code and adding worksheet functions.


Also is it possible to show the pool items inventory on a user form. I want to eliminate anyone being able to tamper with the spread sheet.


Sure, that is not a problem. You can hide a worksheet and use a form to write/read values to/from it.


Also is it possible to have are daily count sheet on one of those tabs?

You can display any value on your worksheet in a form. It's just a matter of setting it up.


Really apreciate your help I have been trying to learn the code myself by using found stuff on the net I have learned lots but not near enough to acomplish this task.

No problem, there are many people here that can help you out. Just take your time to learn VBA and keep practising. Once you get the hang of the syntax and structuring of VBA you'll be creating the most complicated workbooks and applications.

Rembo

CHAD74
12-07-2005, 06:44 PM
The Flowsheet is just a log of when and what we received for reference,

The Inventory is a total of what we presently have on hand not including what we sent away.
The daily count sheet is to track the inventory we have on a crew daily

What happens is we receive equipment from the head office and the equipment is used every day we use a helicopter to deploy in different locations. This is why we us the Daily count so everything that is deployed gets picked up. The pools are the department the equipment comes from. The flowsheet keeps track of equipment movement from crew to crew or back to Office.

I was trying to get a list box that you would select the pool then there would be another drop down for the equipment type. Quantity, and date. Buttons would be sent or received. when sent need to subtract from the selected equipment Inventory. When received need to add to the selected item inventory. Then it would be spectacular if it would all go to the flow sheet as a record of what had been done.

My other problem is some of the equipment has serial numbers that I need to track this is something i definetly needs to be on a user form. I was thinking a check box if checked the number of selected items sent or recieved you would have to enter that many serial numbers. It can not allow duplicates and when sent would have to be a number from the list. I know this can be done i just need some code to help me get started.

Thanks Remo

Let me know if there is any more questions.

Rembo
12-08-2005, 08:31 AM
Hi Chad,

I'll see if I can have a look at it tonight or tomorrow morning (European time)


The Flowsheet is just a log of when and what we received for reference
..
Buttons would be sent or received. when sent need to subtract from the selected equipment Inventory. When received need to add to the selected item inventory. Then it would be spectacular if it would all go to the flow sheet as a record of what had been done.

The flowsheet just shows movements of equipment from place A to place B. What is the relation to your inventory? I mean, if something was returned to you (inventory again) how would that show on your flowsheet?

Rembo

CHAD74
12-08-2005, 10:12 AM
The best way I think to show Received on the flowsheet is positive numbers and negative for sent. Its a log of the transactions. I guess really the flow sheet needs to be the user form that up dates the Crew Inventory. I think that would make it easiest although as you can see on ther flowsheet we receive more than one peice of equipment at a time. If it was possible to keep adding equipment for one transaction and then click post and it updates all the Inventory items.

CHAD74
12-08-2005, 10:32 AM
Thats whait it is the user form needs to be the flow sheet. Hopfully this makes sense. The serial numbers are just numeric there can be up to a thousand of them for 1 item.

Thanks again
Chad

Rembo
12-09-2005, 02:50 PM
The best way I think to show Received on the flowsheet is positive numbers and negative for sent. Its a log of the transactions. I guess really the flow sheet needs to be the user form that up dates the Crew Inventory. I think that would make it easiest although as you can see on ther flowsheet we receive more than one peice of equipment at a time. If it was possible to keep adding equipment for one transaction and then click post and it updates all the Inventory items.

Hi Chad,

Ok, I been looking over the information you supplied and I think I have an idea of what you would like to see happening. I suggest we take this one step at the time.

As for the storage, I noticed that with all the worksheets there will be a lot of double data storage going on. This is not a good idea because the bigger your workbook gets the slower it will run. Excel isn't an excellent data store per se, I think that in this case an Access database would have been a better solution. But all the same, Excel can be used and it can all work as you would like it to.

The first point to take into account is that you don't want double data storing. If it's in there once it's enough. The only thing to worry about is how to get your information out again but that's a concern for later.

Let's start with some program logic.
If you store all transactions in your Flowsheet then this is the only sheet you will need for looking up history. You also need your INVENTORY sheet so you know what equipment and quantities you have in which pool.
To start, let's forget about the other worksheets and focus on these two.

Attached is a workbook. On the Flowsheet you'll find a button to open up a form. Play around with the form for a while and see how it interacts with your flowsheet. Is that wat you want it to do there?
You'll notice I changed the layout a little, I believe all transaction data you need can be found in there. In fact, the column Pool could be omitted but I placed it there because I think it's handy to be able to filter on it later.
You'll also notice I added an autofilter. If you filled the flowsheet with some records, try clicking on the arrows in the fieldnames and see what happens. Autofilters are extremely handy for filtering logged-like data.

For now I just want to know if the flowsheet does what you want it to do.

By the way; if you 'Receive' equipment, does that always go to your inventory (Location To = Inventory) ?

Rembo

CHAD74
12-09-2005, 03:49 PM
Hi Chad,

Ok, I been looking over the information you supplied and I think I have an idea of what you would like to see happening. I suggest we take this one step at the time.

As for the storage, I noticed that with all the worksheets there will be a lot of double data storage going on. This is not a good idea because the bigger your workbook gets the slower it will run. Excel isn't an excellent data store per se, I think that in this case an Access database would have been a better solution. But all the same, Excel can be used and it can all work as you would like it to.

The first point to take into account is that you don't want double data storing. If it's in there once it's enough. The only thing to worry about is how to get your information out again but that's a concern for later.

Let's start with some program logic.
If you store all transactions in your Flowsheet then this is the only sheet you will need for looking up history. You also need your INVENTORY sheet so you know what equipment and quantities you have in which pool.
To start, let's forget about the other worksheets and focus on these two.

Attached is a workbook. On the Flowsheet you'll find a button to open up a form. Play around with the form for a while and see how it interacts with your flowsheet. Is that wat you want it to do there?
You'll notice I changed the layout a little, I believe all transaction data you need can be found in there. In fact, the column Pool could be omitted but I placed it there because I think it's handy to be able to filter on it later.
You'll also notice I added an autofilter. If you filled the flowsheet with some records, try clicking on the arrows in the fieldnames and see what happens. Autofilters are extremely handy for filtering logged-like data.

For now I just want to know if the flowsheet does what you want it to do.

By the way; if you 'Receive' equipment, does that always go to your inventory (Location To = Inventory) ?

Rembo
Hey Rembo

I think we are almost on the same page. The Locations for sent and received would be Crew's or Offices not the pools. Not to confuse you but the pools are cost centers for that equipment so it is just a handy auto filter option like you say. The only problem I can for see is if you receive say 10 fire boxes you can only enter 1 serial number. It would be nice if the user form could say" your sending 10 items " you neeed to enter 10 serial numbers". which go to a database that you can reference by item, location, date and pool number.

The answer to your question is anything sent or received goes to the inventory sent would be subtracted and received would be added.

It sure looks good how it feeds the Sheet. I see what you say one thing at a time. It would be alot easier in person to explain. Access was an option but my users are so use to excel it would be hard to re-train.

When tracking eqipment the serial numbers are critical. If something was lost it would be impossible to locate if the numbers were wrong.

Imagine if you send 3 fire boxes: I List box would list all your fire box serial numbers then the user select 3 numbers click send and they are removed. If the user receives 3 items they must enter 3 serial numbers to the list.
What do you think
Thanks Rembo

CHAD74
12-09-2005, 04:24 PM
Hey Rembo

I think we are almost on the same page. The Locations for sent and received would be Crew's or Offices not the pools. Not to confuse you but the pools are cost centers for that equipment so it is just a handy auto filter option like you say. The only problem I can for see is if you receive say 10 fire boxes you can only enter 1 serial number. It would be nice if the user form could say" your sending 10 items " you neeed to enter 10 serial numbers". which go to a database that you can reference by item, location, date and pool number.

The answer to your question is anything sent or received goes to the inventory sent would be subtracted and received would be added.

It sure looks good how it feeds the Sheet. I see what you say one thing at a time. It would be alot easier in person to explain. Access was an option but my users are so use to excel it would be hard to re-train.

When tracking eqipment the serial numbers are critical. If something was lost it would be impossible to locate if the numbers were wrong.

Imagine if you send 3 fire boxes: I List box would list all your fire box serial numbers then the user select 3 numbers click send and they are removed. If the user receives 3 items they must enter 3 serial numbers to the list.
What do you think
Thanks Rembo


Hope this makes sence

Regards
Chad

Rembo
12-11-2005, 04:00 PM
Chad,

I did a little work tonight. I haven't quite figured out yet what to do with the serial numbers. You could include them in the flowsheet, logging each serial number on a line but that way your worksheet will quickly fill. Another option is to write them all in one cell, what do you think?

Rembo

CHAD74
12-11-2005, 07:32 PM
Sure looks good Rembo

THe serial numbers will have to feed onto another sheet Like a database. Item, Serial # Date Modified. I was working on a message box with a list of the numbers also. The list boxes and everything looks good although I did notice the Pool stayed at 10 no matter what, small problem though.

Thanks again
The Vision I had is possible

Rembo
12-12-2005, 03:35 PM
Hi Chad,


Sure looks good Rembo
THe serial numbers will have to feed onto another sheet Like a database.

Just as a suggestion I coded a serial number worksheet with corresponding routines to handle them. I also changed some of the listboxes in userform2 to comboboxes. You might want to try play with them to see if that is any good for you. Note that there's isn't much error trapping yet so use with sense. E.g., if there are 5 serial numbers in the worksheet listed, then you can't send/receive 6. Makes sense in the real situation as well.


.. I did notice the Pool stayed at 10 no matter what, small problem though.

Ah yes, fixed that while I was at it.

Rembo