Comboboxes Questions (linking and automatically updating)

I have the following workbook (attached) and it is in various states of disarray but I have two questions concerning it;

First, there are three ComboBoxes that need to be linked to each other in the form. When I say this, I mean that depending on the user, it will be easier for them to enter the property name, versus the property number or AFE number or visa versa. Is there an easy way to have it so that when any of the three boxes (WELL NAME OR YARD, PROPERTY NUMBER, or AFE NUMBER) are selected, the other two populate with the corresponding data?

Second, there is a tab to enter new property details (the tab is not finished) as additional wells go on line. I can get it to the point where the input data will appear on the bottom of the PROPERTY DATA sheet. However, how can I create a dynamic range that will include this new information in the corresponding ComboBoxes on the first sheet automatically? Or, if there is a better way then a dynamic range to accomplish this, I would love to hear that too.

Greg,

Thank you for the response, the code looks good but I'm fairly new to VBA and don't really know what it means. I was wondering if you would mind posting it again with some more extensive comments. I know that things that are elementary to you might seem foolish to put in the code but I would really appreciate it.

EDIT
I have included my new code on the next post, and you can see specifically where I am a little lost. it works great but I'm not entirely sure why and I would like to...
END EDIT

Also, when I was reading up on Forms, the article did mention changing the names of the controls but I opted not to. My thinking was that using comments and actual full words to describe the control would be easier for me personally. I may find that this isn't the case as I go forward but for now it seems to be working for me.

Allright, here is what I have now...

Please Login or Register  to view this content.

The next step is to improve upon the design a little more with a couple more tweaks;

I plan on updating the above CreateDropDownLists to include 'tubing description' and 'supervisor' incase that ever needs to be updated.

Also, I would like to have a better system for a date ComboBox. I have seen on websites (travelocity, etc) where an actual calendar would pop up with the current date initially highlighted. Is there any way to get this on this form?

Another question about this workbook...

It would be nice if the ComboBoxes would be in either alphabetical or numerical order. This may get tricky with the three 'linked' ComboBoxes at the begining of the form but I know somebody out there can do it.

As for the other ComboBox that would be nice to have alphabatized, there would probably be some way to update that every time a supervisor's name is updated through the new supervisor tab.

Another question about this project... this is a "to-do" list of sorts for myself...

I have created another sheet called "AVAILABLE TUBING" and would like to list each of the wells on this sheet and then put all of the data on a per well basis on one line. I can't do a real good job explaining it, but with the master sheet, the well information will be under both sending and receiving and I want both sending and receiving to be categorized by well. I have included an updated version of the project for your viewing pleasure.

Please, if you have any questions or want any clarifications about anything I have posted just let me know.

First off, thank you! You have been a huge help.

Now, the available tubing spreadsheet has evolved throughout the day and here is what it is going to entail as I see it now (here are the anticipated four column headers);

∙ the property information (including the property number and AFE number)
∙ Joints of tubing ran in the well (as entered on the ‘FROM’ form)
∙ damaged joints of tubing left on location (as entered on the ‘FROM’ form)
∙ total joints currently at location (this is the tricky one)

Actually, they're all tricky to me, but here's why the joints on location will be tougher than the rest...

When the tubing is delivered to location, it may or may not come from one place. Each separate location that tubing is delivered from will have a separate delivery form that needs to be filled out (the form you and I have been working on). That being said, the 'total joints currently on location' box needs to include every form that is filled out. I think the easiest way to describe it would be to give an example;

Say we want to know at all times the inventory on WELL A. When a (may be one, may be several) form is filled out with WELL A in the TO LOCATION box, condition A, B, and C joints should be added to the total in the ‘total joints currently on location’ cell.

When a (may be one, may be several) form is filled out with WELL A in the FROM LOCATION box, the ‘total number of joints ran in the well’ need to be subtracted off of the total. The damaged class E joints need to be placed into the ‘damaged joints of tubing left on location’ column and subtracted off the total. Then the condition A, B, and C joints should also be subtracted from the total. The condition A, B, and C joints on this form will also be included as the TO LOCATION for another well and on the cycle will go.

After all of the transfer forms are complete, the number of joints left on location should equal 0. I have it set up in my most recent spreadsheet to have some conditional formatting if it is anything other than 0.

I think that might be almost clear…

Also, I can’t get your date button to work, I’m not really sure what the problem is but it says “Compile Error: Method or data member not found” and the debugger stops on the line

Please Login or Register  to view this content.

If there is anything else you need, as always just ask. I do appreciate the help, this would have taken me years to complete myself!

Oh boy, one more thing...

The boss dropped this on me today. Initially, all of the wells and their corresponding property numbers will have one AFE number. BUT eventually there MIGHT be a time where one AFE has expired and tubing is being transfered back to that location. Anyways, Is there anyway to include the AFE number on the dropdown boxes for the property name and number? This way if there are two entries for the MAY 1H well property number 626226 with AFE numbers 123456 and 654321 it will show up as either;

MAY 1H - 12345 or MAY 1H - 654321
626226 - 12345 or 626226 - 654321

This would help the data input person from choosing the wrong MAY 1H well. If you need more explanation, as always please let me know

The total joints on site should be a single box with the most up to date count in it at all times without any additional details.

The major propblem I see with putting that total on the MASTER SHEET would be the redundancy of the same well name over and over in both the TO WELL and FROM WELL rows. I'm not convinced that it can be done in a manner that will be as user friendly as it could be.

A DropDown Box for this would be fine normally, but having a well name in each row is preferred because it would make it that much easier to run through the whole list (via filter or scrollbar) and see where the joints of tubing are.

I will include my most up to date version of the workbook with this post. Also, I have messed with it some as I didn't like all of the 'DATA' sheets on the bottom of the page. This has created longer than desired DropDown Boxes for my ComboBoxes on the form. I believe it is from this code;

Please Login or Register  to view this content.

I think I can change this to mimick something like this and make it work;

Please Login or Register  to view this content.

The Available References I have selected are;
Visual Basic for Applications, Microsoft Excel 12.0 Object Library, OLE Automation, Microsoft Office 12.0 Object Library, Microsoft Forms 12.0 Object Library, and Ref Edit Control.

I have gotten several small questions built up and I need to get to work on them asap so I am going to start other threads with the specific minor questions I need and include the answers on this sheet.

Here are the threads I have started elsewhere:

SOLVED:

http://www.excelforum.com/excel-prog...ml#post2106851
http://www.excelforum.com/excel-prog...ml#post2106860
http://www.excelforum.com/excel-prog...ml#post2106963
http://www.excelforum.com/excel-prog...ml#post2107244
http://www.excelforum.com/excel-prog...omboboxes.html

I like it but I don't know if it will suit our needs well enough. Once a month we are going to go through the list of wells we have and look up any tubing on location and take an inventory. Once we get tens, then hundreds, then several hundred wells, I don't think that anybody will want to click the 'select property' combobox repeatedly.

It's pretty close though. Ideally it would just be a list of every well including the property number (No AFE number) with another three columns of buried tubing, damaged joints on location and excess joints on location.

The reason that the damaged needs to be separated from the excess is for the accountants and for cost effectiveness of transporting. The accountants can't justify billing transferring tubing that is classified as junk from well to well to their partners and our group can't justify paying one truck to go pick a joint off location. So as a compromise every time a junk piece is encountered, it is left on location and noted so that we can pay a trucking fee one time for somebody to pick up a unit or so from several wells.

Anyways, It looks good but I don’t think it will suit our needs as well as it could.

Thank you so much for your continued help, it is truly appreciated and I look forward to hearing from you.

Almost to an acceptable stopping point for this project!

Seeing the quick response I've had by posting smaller questions, I think I am going to call this thread solved and create a new post about the inventory or available tubing sheet.

LINK:

http://www.excelforum.com/excel-prog...ml#post2108039