help with complicated formula

[SamT]

i would be interested in seeing your thoughts on the time-sheet but there are some thing the i saw on the last sheet you sent over that i would not need which i mentioned. however i would have one question and that you would be this: would you need to change the layout of my userform or is it ok. see i did/do like the idea of being able to view history(s) of pay, night out history etc. and also have the ability to change a night out location if things change.

as you rightly stated before i am new to this and yes i did try to add alot of features in this to try to help the not just myself but other driver. my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me. im really sorry SamT

Oh yeah, beginners enthusiasm. I remember it well.

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me

The only way I have found to deal with these idea is to write them down. Go back to the last book I uploaded, Truck Driver Report 02.xls, and insert a new sheet behind the Control Panel sheet. Name it "Ideas." Stretch Column "B" to the Right until just enough of Column "C" is showing to act as a border. Format Column "B" with "Wrap Text." In Column "A" write a brief, one line, description of the idea. In the next Row, column "B," write as much about the idea as you need to. Skip a row and repeat with the next idea. I still use this process when developing an App.

my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

In my mind, I see the App as an integrated object, where the user can see all the relevant sheets at will. I would have the workbook open to the Control Panel sheet, from which he can either open a form, or browse the sheets. I would only let him open a form from the control panel. This reinforces the idea that the CP is the way to edit the sheets and provides a consistent User Interface. A consistent navigation system is important to a well designed App.

IIRC, I haven't spoken of your current UserForm yet. That is because it is still too early in the App design process to put any time in thinking about the Form itself. I think of the current version of the UserForm as just another "Idea" Sheet. It's sometimes handy as shown by the ease of my getting a list of Data Points for you, but not necessary.

Briefly, these are the steps that IMO are required to develop an App like yours:

I: Develop the database

1. Create a list of every possible data point you can think of. See my "Company Database" sheet and your "VDO" Sheet. Name this sheet "Data Points List"
   
   • Use two columns, the first for the name of the data point and the second for a brief description of it. The second is mostly for me, but you will find it useful, too.
   • The Data Point "Date" should be at the top of the list.
     
     • Its description is "The Calendar date of which all records are pertinent to."
     • It is the only Data Point (DP) with the simple name "Date." All other Date names should be uniquely descriptive such as "VDOPrintDate," "ShiftStartDate," ShiftEndDate." etc. I'm not saying that you will have ShiftDates, that's just an example.
     
     
   • Using the description column, insure there are no duplicate data points. Indicate in the description which different place this DP is used.
   
   
2. Using a different sheet for each group, organize the DPs into related groups like the "Company Database" and "VDO" sheets.
   
   • Name each sheet according to its group
   • Put the lists in row 5 or 6 on each DP group sheet.
   • Using the Descriptions, make sure that each group sheet has its own copy of the relevant duplicated DP.
     
     • DP name (calendar) "Date" should be at the top of each groups sheet's list.
     
     
   
   
3. Review all the groups to insure that the requirements in steps 1 and 2 are met.
4. On each Group sheet, copy the list of Names and in cell "A1" PasteSpecial >>Transpose.
5. From now on, we will no longer refer to DPs and Groups, They have now transformed in to Field Names and Database Tables.
6. Format each Date and Time Field (Entire Column) to suit.
7. Save the workbook with the name "Database Descriptions."
   
   • Anytime in the future when you modify a database, edit this workbook to match, adding notes to describe what, when, why.
   • Keep Data Points List up to date.
   
   

Normally I would allocate several days for all the above, but you (and I helped a little bit with the list of DP Names,) have already done most of it. By the time you complete the above, you will have a very good feel for what your App can and should provide any truck driver and most important, what Form(s) you will need. The completed "Database Descriptions" is half the work of developing the UserForm(s), so do not think that it is not necessary.

Each of the below major steps will also take a few days. Only a few because of all the preparatory work done in step I. As you get to each step below, I will be expanding the minor step lists, so you will know what to expect.

II: Lay out the Userform(s)

• Save book as "TDR.v.01." Suggested name.

1. Use the Data Points List to know what controls you'll need.
   
   • More info later
   
   
2. Open "Database Descriptions" and add the sheet "vbaList" to the end.
   
   • Delete all DP lists, leaving only Field Names.
   • Complete "vbaLists."
   • Make Lists of Suggested Form Control Names
   • Add database type code to Database sheets and "vbaLists."
     
     • I already have this code, that only needs localization to work in any DB type sheet.
     
     
   
   

III: Code the Form(s)

1. Increment the Version number to .02

IV: Add Reports Sheets, Increment Version number
V: Repeat II, III, & IV as needed.
VI: Beta test.
VII: Repeat step V as needed.
VIII: Sell the Application
IX: Repeat Step VII as needed

You will have noted that with each major step, the work book gets saved with a new name. It is not until the end of step V that it actually gets a working name. Be sure and keep a copy of each workbook with a different name, even if it's just a minor revision number change.

I usually keep my projects in their own folder with the following sub-folders:

• bas
• Archives
• Helps
• Examples

bas is where I export to and import from any modules I want to move from book to book.

Paul, take whatever time you need to understand what is in this post. As questions about it if you need to, but really try to fully understand it.