Post Holding thread for my own use

The Index says it was started by Paul, because his is the earliest dated post I merged into the thread. In fact, I created the thread. SamT

Good Threads post



One master and 30 other WBs -- is the Master the only one with code? Probably not, no one is ever that lucky.

<<Personal Opinion Mode = On>>

For something like that, I'd think first about the architecture of the whole thing first, before I tried to decide if more With/End Withs were needed, etc.

I prefer to seperate the different types of entities some how

1. Control (user commands, system configuration/status,etc.)

2. Data (possibly entry and validation as well as storage)

3. Calculation (possibly by updating other cells or worksheets in the Data)

4. Presentation (reports, pivot tables, charts, a worksheet with JOIN-ed data to make reporting filtering easier, etc.


I typically find that I usually end up going Top Down and Bottoms Up. By that I mean as I worki from the top level, I find that I need a subroutine to do something. I try to make it very general purpose since I know that later I'll probably need to make it smarter to handle something else that I haven't thought about yet. That way I have one sub that will handle many different situations well, instead of many subs that only do one thing.


VERY simple example, in one place I might need to validate that 0<X<100, in another that -10<Y<25, etc. A function: IsXValid(X) works at first, but then later I would need IsYValid(Y).

I try to be alert to oppertunies to make a previous function a little smarter, instead of just quickly writting another one. Instead of writting IsYValid, I'd make the first one IsNumberValid (X, Optional LowVal as long = 0, HiVal as Long = 99999)


Silly example maybe, but the concept works for me, even if it's not 100% effecient and involves re-work.


I ALWAYS go for maintainablity and clarity, even if the code in not tuned to maximum efficency. After all, I'm the person who will have to read the source code in 6 or 24 months and try to figure out just what the heck I was doing

<<Personal Opinion Mode = Off>>


Getting off my soap box now

Paul

Not a bad First try.

Now Johnny, you've been a bad boy, I want you to write, on the blackboard, 100 times,

Quote:

---

Function Follows Data Structure
All Names are the Same. Except Prefixes
Forms follow Work Flow

---

:bug: :rotlaugh:

Seriously now:

Function Follows Data Structure
You don't know what to do or how to do it until you have your Data Structured.

Data must be structured in a neat, concise, unduplicated way. The Structure can and should be as identical as possible when one Table (Sheet) is used for Records (Rows of Data) with different statuses than another. See sheets "Outstanding POs" and "Completed POs" in the Archangel attachment in my post above. Get those two arranged and formatted the way you prefer/need and fill in some dummy data on "Outstanding POs" with several statuses. First see next para, this post.

The Sheet "vbaLists" should hold lists of every possible value for the value types shown in the header row. These lists will be used to populate Drop Downs in the Forms, so Users cannot enter wrong values. Also add any columns you think you may want as validations or dropdowns.

All Names are the Same
After you've designed the Data Storage sheets and the Lists sheet, make a list of all the Unique header names from all three sheets.

Using this list make some Range names that are very similar to the sheet header names, except that all spaces are removed or replaced with underscores. You can use common abbreviations and memnomics such as PrtNo for "Part Number" and PrtDesc for "Part Description," "MfgPrtDesc," etc.

Now choose prefixes that represent each sheet name. For example "open," comp," and "list" or "vba_." No Caps Please. Note that all sheet prefixes should have the same number of characters.

Using the Sheet appropriate prefix create defined names for each column on each sheet. Example openPrtNo, vba_PrtNo, and compPrtNo. Note pattern of CapLettersInNames.

UserForms generally consist of Label/input box pairs. The labels usually don't need to be named, but the input box names should be identical to the Range names without the prefix. Input boxes can be drop down ListBoxes or type-in TextBoxes. You can use any vbaLists Validation lists to validate TextBox entries and will use vbaLists dropdown lists to fill the ListBoxes. the ListBox code might look like this, where lbx is a standard prefix for a ListBox name:[vba]lbxPrtNo.RowSource = vbaLists.Range("listPrtNo")[/vba] and the code to record that part number might be[vba]Sheets("Completed POs").Range("compPrtNo").Cells(NextRow) = lbxPrtNo[/vba]
See the pattern? All Names are the Same. Except Prefixes. Make all Form prefixes the same number of characters. Makes coding a breeze. Just by looking at the input box name, you know exactly where to get, and where to put, its' data. If you study the code in the UserForm App attachment I gave you you will find examples of how to load and store the entire form with one loop.

Forms follow Work Flow
If all PO numbers are unique, when updating a record, you can have the Form itself fill in most input boxes by merely finding the row of an existing PO. Make the PO number input box the first one on the form. If its a new PO number, well its not that hard on the user compared to the value an auto fill gives. About a third of the time the only changes the User makes is to update the Status, maybe that box should be next. OR, what is the first item on the (paper?) form the user is looking at to complete the UserForm? Maybe that should be the next input box, etc.

Creating the Form
Looking at all the example available it looks like the only input boxes that cannot be ListBoxes are quantities , dates, and the PO number itself.

To a blank Form Add one Label and one TextBox. Align them, make them appear to be the same height, set their properties. Add no text or names at this time. Use the Ctrl key and the mouse to select both of them. Right Click and drag them to just below where they are and select Copy Here. Repeat until you have a pair for each needed TextBox type entry.

Drag the mouse (left button) over all the labels and Text Boxes, Left grab a cross hair handle and drag them over out of the way.

Repeat the above for the number of ListBox types you need.

Remember that Forms Follow Workflow. Grab a TextBox/Label pair and drag them to the top of the Form, about the center area or exactly where you think they'll go. Type in the Labels text, "P.O. Number", and expand it to the left to show all the text. Name the inputbox "txbPONum" or whatever, and set its TabIndexNumber to 0. When the Form starts, TabIndex 0 will be selected for input.

What is the next input in the workflow? Grab that pair, move them as desired, name the box and set its TabIndex to 1. When the User presses Tab or Enter while in the tbxPONum box (TabIndex 0), the box with TabIndex = 1 will be selected for input.
Repeat for all Label/Input Box pairs. If you rearrange the input boxes, select them by Work flow and renumber their TabIndexes.

Coding the Form
You will need to refer to the App attachment I posted in a previous post.

Copy all the declarations, (What is above the first sub,) in the Form code in the App to your Form code.

Right click on the Form and select View Code. At the top of the Code Pane. in the box labeld "General,"and in reverse Work Flow, select a lbx* control. VBA will automatically insert a Click Event Sub for that control. In the right hand box at the top of the code pane, select "enter" and it will insert an Enter event sub for the control. Delete the Click event sub and select the reverse Work Flow's next listbox, and repeat. When done you should have a series of ListBox Enter subs in WorkFlow order.

Click on UserForm in the "General" box and insert the Initialize sub. Paste
"InitializeControls
LoadLocalCollections" in it.

Find an Enter sub in the App example and paste that code line into all your Enter subs.

Paste this in[vba]Private Function LoadLocalCollections()
'Collections used for various Form activities

Dim Ctrl As Object
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "ListBox" Then ListBoxes.Add Ctrl
If TypeName(Ctrl) = "TextBox" Then TextBoxes.Add Ctrl
IntakeBoxes.Add Ctrl
Next Ctrl
End Function[/vba]
There are many more subs and functions in the App that you can paste into your code. Be sure to look at the workbook and worksheets code too.

See ya soon.

Steven,

Worksheets are cheap. Put your footer on a new sheet all by itself. Format the sheets columns exactly like the sheet the footer gets pasted to, hidden and widths.

Do not use "Merge Cells. Use Webdings Font, lower case "c" for checkboxes.

Starting in cell "A1", design your footer as you like, include any empty border rows/columns all around.

Including "A1", select the entire footer with empty border cells. On the Excel Menu, Insert >> Name >> Define, Name the Selected Range "DocFooter." If you think you may ever want another footer, append a numeral to the name. Don't use "footer" as the name without 'pre-' or 'suf-' fixing something to it.

The code snippet above now becomes[VBA]Range("DocFooter").Copy Sheet1.Cells(dr + 1, 1) [/VBA]




Fortunately you have a working App "Live," so you can take some time to think about what you really need to do do make it a good one.

Structured Code Requires Structured Data.
Or; Someday You'll Want SQL.

Data Is For Code and Reports Are For People.
Or; Keep It Separate, Smarty.

Data is Forever, But Reports Are Kiss and Tell
Or; Intransient vs Transient.

Reports are forms and forms are reports. I use "Reports" for both except when talking about UserForms. for me, Form always means a (VBA) MS UserForm.

I'm going to refer to this Post: http://www.vbaexpress.com/forum/show...php?t=46847And this App:UserForm App.xls from that post.

The Post is to someone else who is designing their first App and the Download is an (incomplete) App I was making for someone. It contains in one book, a Report sheet, (transient data,) a Report Database, (permanent new data storage,) two value look up sheets, a Locations Sheet, (fixed data,) and a Data Entry Form definition sheet. The data structure and data retrieval code in it will be of interest to you.

Some Definitions for you:
Database = A group of Columns and Rows
Field Name = Column Label or column Range Name.
Field = A Column in a DB
Record = A Row in a DB
Record Field = One cell in a Record
Keys, primary and secondary = Unique values that identify unique records
Index = Key

From what you have said about your operation, it's a pretty good sized business. I would suggest that you consider putting Customer information in one DB with an Index number in Column A. The Index, or Primary Key is used to look up relevant records in other DBs. You can also have DB's for employees, product codes, locations, (by product code Index,) and Destinations. Any data that can be (semi-) permanently listed.

I realize that this post in Intense 'n Dense, so I'll check back in a few days.

Thread: help with complicated formula



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.

All that is really left for you to do on the DBs is figure out the names that I couldn't and fill in the column Labels and the DB Descriptor tables, (those are the three-column lists I put in row 5 or 6, column "A", and below on each DB sheet. Leave those lists there! Note that the name of this workbook is DB Design! It is a permanent record of the DB layouts.

Note that the control names and the Column Names should closely resemble each other. Try not to use abbreviations. New Users might not understand them, I certainly won't, and they make coding harder. Obviously, a well known abbreviation is okay, (ODO for odometer, Rcvd for received, etc.) Look at all the examples I provide on the DB sheets. (DB for database) :yes

Note that the workbook is an xls type book. Always try to save all your coding and design and development work in older style books so that you can sell your product to drivers who are still using older versions of Excel. I recommend saving all books as Excel Version 2002 or 2003 if you can. All the books I send you will be in version 2002, which will probably be good enough, because I learned with version 97 and tend to stay with that code base. IF the book still works when you open it after saving it as version 2000, you know that it will work in that version (2000) of Excel.:cool:

Only design Sheet style reports for now and do not worry about getting the data on the report. Just put the DB and column Name in the blanks.

Paul,

A Form is where one enters data.

A Report is where one views data.

So... The reports you need to make are one for each SET of data you want to see at one time.

IThere are two species of Reports; What a User needs to know and what are nice to know. Since your time to complete this App is growing short, concentrate on what he needs to know. It won't be a problem to add any more Reports later. In fact it will be quite easy and you probably won't even need my help after you've seen how to do it once.

The Reports finalize the Databases and the Databases finalize the Forms. Even after the App is put into production it is easy to add to if it is properly designed.

Keep the Report simple and to the point.

Paul, consider your official drivers log. When you need information from it for some reason, what information is it that you need? Those bits of information are "need to know" Reports

There is probably a couple of situations that make you look in the log. Each situation probably makes you look at some different information than the others. Those situations are different Reports. Different Reports can have the some bits of the same information on them. That's how you know if you should combine two reports; if most of the bits are the same and only a few bits are different, then combine them. But if most of the bits of information are different, then they should be separate Reports.


Designing an App that has one form and two reports takes as much time as adding three more forms and 10 more Reports.

If it is very simple, you won't feel bad about giving it to 5 or 6 of your mates for Beta testing and promising them that if they are good beta testers, they can get the updates gratis. You do not want to be selling anything that has not been Beta tested, because it will ruin the reputation of your App forever.

A good Beta tester:

Actually uses the App;
Promptly tells you when you App errors;
Tells you what they don't like about the App;
Tells you what they do like;
Tells you what additional features they would like to see.

Paul, You have great ideas. Remember, I've seen your work.

You are the architect, I am the builder. You tell me what and I can code it. If you tell me too many whats, I can tell you that it will take too long to code. I can tell you, the Architect, That you need a window, (Shift Date) in every room, (Database,) and that the window sizes have to come from the same Input box.

If I build something, you have to tell me if it fits the need. Take that Estimated Pay Report; Is it good enough; Does it show more or less than needed; Maybe that the driver only needs to see the total expected take home pay. Does he need to see something more, (I dunno, maybe the company's NI contribution?)

Imagine you have 4 friends, one drives for a company, one for an agency. One only drives part time, and the last owns his truck and drives for ??? I dunno how that works.

• As each one of your friends is getting ready for work, what do they need your App to show them?
• As each one first talks to Dispatch, what does he need your App to tell him?
• As each one starts the engine in his truck, What does he need from your App?
• While the truck is being loaded, what do they need to see in your App?
• When any of your friends stops for a break, what does he need to know. What about after the break?
• How about if they stop for petrol?
• Do an Overnight stop? The next morning?
• Have a tire repaired?
• Make the delivery?
• Leave the truck in the company yard at the end of the shift?

Save that list. Add the bits I left out. Put it on four spreadsheets or sheets of paper, What ever works for you. Name each sheet with the drivers job and add the App requirements for each

It will be very useful when we design Forms, too.

Thread: Sales Returns for FIFO

Project Design.
Structure Precedes Function. When designing your DB Sheets, think about the entire business process, from Purchasing to Pricing and proceeding all the way to EOY accounting. Insure that you can find each piece of information needed on some database sheet. Think about the process frontwards, backwards, upside down and inside out. It is so much faster to get it right the first time than to have to rewrite all the code a second time.

Get your DB sheets done as best you can and only then start work on the UserForms. Share your workbook with us and we will do our best to make sure your DBs are complete and we will get your code to work.

From:VBA code to transfer data to different workbooks Good Thread

To insure understanding, I am restating your requirements

You have

• WorkBook named "Master Log"
• Where X = Number of employees
  
  • Master Log has 2 + x sheets
  • Sheet1.Name = "Master Log"
  • Sheet2.Name = Reference
  • Sheets(n thru X +2) are named in pattern Employee(n).Name
  
  
• You also have X (+ exEmployee.Count) Workbooks also named Employee(n).Name
  
  • Workbook Employee(n).Name has one sheet also named Employee(n).Name.
  
  

You need to perform

• Transfer data from Certain Ranges in
  
  1. Book Master Log, Sheet Employee(n).Name
     
     • to Book Employee(n).Name, Sheet Employee(n).Name
     
     
  2. Book Employee(n).Name
     
     • to Book Master Log, Sheet Employee(n).Name
     
     
  
  

We need to know

• Where everything is stored or saved to.
• what triggers each transfer
  
  • Click a button,
  • Change Worksheets in master Log
  • Open a Employee(n).Name Book
  • Use a Menu selected Macro
  • Some other event or selection
  
  
• What happens to books Master Log and Employee(n).Name at the end of the fiscal period
• What happens to books Master Log and Employee(n).Name at the beginning of the next fiscal period

Code requirements

• All Items with Name = Employee(n).Name are identically named
• A list in Book Master Log, Sheet Reference of current Employee(n) Names, as ex-Employee Books and records must be kept for at least the end of the Fiscal period .

I think I see what you need.

As a "Project Designer," I suggest the following scenario

In a location of your choice:
Folders and Files

• A folder named Templates
  
  • Restricted to Adminstor (and Supervisors?)
  • Template_Supervisor.xlsm
    
    • w/UserForm to perform Supervisor's duties.
    
    
  • Template_Employee.xlsm
    
    • w/UserForm to perform Employee's duties.
    • Sheet 1 is named Assignments in all Employee's books
    
    
  • Template_MasterLog.xlsx
  • Copy of Administrator's FiscalEnd.xlsm
    
    • w/UserForm to perform admin tasks.
      
      • Can also include Supervisor's duties UserForm
  
  
• A folder named Folder Assignments
  
  • Supervisors Save Employee books here
  • Employees Open their workbooks from here
  
  
• A folder named Folder Returned
  
  • Employees Save their workbook to here
  • Supervisors Open Employee books from here
  
  
• A folder named Folder Archives
  
  • Should be retricted to Supervisors and Administrators
  • Stores Old Employee books, Named fiscally
  
  
• A folder named Folder MasterLog
  
  • Stores MasterLog.xlsx Book(s) (Old Logbooks named Fiscally)
  
  
• Supervisor(s) Workbook(s) [Copy(ies) of Template_Supervisor.xlsm]
• Administrator's FiscalEnd.xlsm

The Fiscal Ends Tasks

• Employees open, complete and save all Employees workbooks to Returned. Do not reopen till OK'ed.
• Supervisors Open all Employee's books, Update MasterLog, Save all Employee's books to Assignments
• Administrator Uses FiscalEnd.xlsm to
  
  • Rename and move all used Workbooks to Archives
  • Create new MasterLog.xlsx
  • Create new Supervisor('s) Workbook(s) from Template_Supervisor.xlsm in Templates
  • Create new Employee books From Template book in Templates, using Employee list from list in a Reference Sheet
  
  
• Tasks can be restricted to particular time frames. Or not. Your choice.

UserForm notes

• All UserForms
  
  • Follow all folder Restrictions
  • Prevents User from viewing actual worksheets

• Employees UserForm
  
  • Only shows incomplete assignments
  • Ability to select specific assignment
  • Commands and inputs to perform all Data entry
  • Save book command
  • Send to Supervisor command
  • ? Recover and edit book from Returned Folder ?
  • Edit command to edit all entries on all assignments since last "send to supervisor" event

• Supervisor(s) UserForm(s)
  
  • Ability to select specific Employee Book
  • Commands and inputs to perform all Data entry
  • Send to Employee command
  • Edit command to edit all all Books till in Folder Assignments
  • Can View all worksheets in Employee's books and Master_Log book
  • ? Can delete completed Assignments from Employee's books ?

• Administrator UserForm
  
  • Can include all or some Employees forms abilities
  • Can include all or some Supervisor(s) forms abilities
  • All commands and inputs needed to perform all duties and tasks

All Folder names and workbook names above are suggestions.
All restrictions above are suggestions.

This is an overall, upper level idea of the way it should done. We must agree on the exact structure of this level before we start coding if we want to get the project done in the fastest manner.

The next approval will be the overall look and feel of the UserForms. Believe it or not, the actual coding will probable take less real time than the rest of the project.

I think that the way you re envisioning the system is that

1. Each Employee has a workbook, (so that they can't see each other's cases?)
   
   1. They manually enter data into each cell on each row.
   
   
2. All Supervisors share one master workbook, (So they can all monitor the master log sheet?)
   
   1. They manually enter data into each cell in each row.
   2. The VBA in the Master book transfers data from the Master into all the Employee books.
   
   
3. At some point in time the Employee books have to be saved back a folder that the supervisors and employees all have access to.
   
   1. Randomly thru the workday
   2. In the evening at quitting time
      
      • The Supervisors have to work late
      • Or, the assignments must stop early
      • Or, the Supervisors must start early
      • Or The Employees must start the Assignments late.
   
   
4. Any given workbook can only be opened for data manipulation by one User at a time.
5. With one Master Log book
   
   1. The Master Log book is locked for use by any other Supervisor for the entire time it is already in use.
      
      • Only one Supervisor can assign cases at a time
      • Supervisors must coordinate with each other as to who is to open the Master Log book
   
   
6. If using only one folder to store all workbooks
   
   1. All Employee books must be saved, (and no assignments worked on) by a set schedule
   2. OR, Employees must inform the supervisor when their book is not in use by them
      
      • Their Supervisor must inform them when their book is available.

I had already considered the above before or while writing the suggested scenario in post #6 above.

Quote:

---

Is there no way that a VBA code automatically runs once the workbook is open? The code that I have in there now automatically updates the master and employee logs but it's not VBA it's just a formula that is to a specific cell.

---

Yes. In fact that is what I plan to use to open and run the VBA UserForms I mentioned in the Suggested Scenario.

Quote:

---

but the Master log needs to be able to be accessed by any supervisor due to there being multiple supervisors viewing and monitoring this workbook.

---

They need to all be able to use the Master Log, but in the Scenario, the Master Log would only contain Data. All VBA code, Including UserFOrms, would be in each Supervisor's own Workboo, which would not conatain any Assignment data.

Quote:

---

What I don't think is there being so many folders, it kind of seems like too many workbooks and folders being created

---

The Assignments and Returned folders address issues 3 and 6 in the list above. the use of multiple Supervisor's books address issues 3, ,4 ,& 5 in the list above.

Quote:

---

The supervisors don't technically need to be able to view the employee's workbook or make changes.

---

Ok, we won't code for that ablility

Quote:

---

I really am looking for the Master workbook (which the supervisors will access and add the assignments or make adjustments) to be able to communicate and send data to the employee's workbooks.

---

Which is more important ? That the supervisors can send assignments to the Employees or that only one workbook in the system can do so. See issues 4 & 5 above.

Quote:

---

with the formulas I have now that I really like, but the problem I am having is that you can't delete a row because the formula is only for those cells, so the formula doesn't move or change when the row is deleted

---

That is not an issue when using VBA to handle the data.

Code:

---

since the formula is on only half of the master log and half of the  employee's log, I have to open both logs to make the changes if I am  reassigning cases.

---

The Scenario in post # 6 already addresses those issues.
In the Post #6 Scenario:

From the view point of the Employee:
When they open their Assignment workbook, A Data Entry Form is displayed with all open assignments listed. When they click an Assignment in the list (by name, date/time, case number, or however you want them listed.)

All pertinent information in re that case is magically displayed in the most convenient layout for their work. While entering dfate, sometimes they just click a button and the pertinent value is automatically entered, sometimes they select a value from a list, and sometimes they have to actually type in a value.

At the end of their work on a case, the click a button named Done or one named Next Case, or even just click another name in the List of Cases.

If there are no more cases in the list, they click a button name Refresh and like magic, the list fills up again. at the end of the day, they click a button named Close Workbook or Exit.

From a Supervisors Viewpoint:
When he opens her Supervisor Workbook, a "Control Panel" magically appears where he can select to View Master Log, or Make Assignments.

When she selects Make Assignments, a Data Entry Form appears with a list of all employees he is responsible for. There is also a button named View all Employees, (this allows any Supervsor at that computer to make assignments for any Employee.)

She then selects an Employee from the list and a Data Entry form appears that operates just like the one in the Employee's Assignment book.

When done with that Employee, he can click one of several buttons, Save, Close, Exit, Next Employee, Back to Control Panel, View Master Log, or as you want.

From the viewpoint of the Project Designer:
All UserForms shall be designed from the viewpoints of the User and the Business goals. Period.

Make the Users tasks as easy and simple as possible. Accomplish all the Businesses' goals, include preventing as many preventable data entry errors as possible.

Please keep your issue and ideas coming. We need to keep this up until we are both happy.

The next step is up to you.

Watch or better yet, video an Employee while they start and complete an assignment using the current system. Do this with three Employees.

Carefully analyze the order in which they perform the steps

Repeat for three supervisors.

That is very important to making the best UserForms for workplace efficiency and User comfort. The three or four hours it takes you (or a minion :D ) to do the note taking/recordings will pay for itself in less than one fiscal quarter. It probably will prevent us from having to redo any workbooks and UserForms because we got it right the first time.

Show us those analyses.

Which reminds me: Use the VBA Menu >> Tools >> Options >> Editor Tab and check everything in the Code Settings Frame, then manually add "Option Explicit" to the top of all your existing Code pages. That will now be there on all new pages

While you are there, on the Editor Format Tab, you can change the colors of various types of VBA Code Words. I changed the Identifier, KeyWord and Comment colors, YMMV.

On the General Tab, I like to check the "Break On All Errors" box.



So I've been reviewing all of your new code that I can find, which isn't that much. Is it possible for you to share all the latest versions with me?

Which reminds me: Use the VBA Menu >> Tools >> Options >> Editor Tab and check everything in the Code Settings Frame, then manually add "Option Explicit" to the top of all your existing Code pages. That will now be there on all new pages

While you are there, on the Editor Format Tab, you can change the colors of various types of VBA Code Words. I changed the Identifier, KeyWord and Comment colors, YMMV.

On the General Tab, I like to check the "Break On All Errors" box.

This post was copied from another thread.

When designing a Project, it is Best Practice to follow some simple steps in order:

1. Gather and Record Data
2. Design Data Display
3. Determine Rules and Constraints
4. Lay Out Data Input
5. Write Code

Steps 1 to 3 are yours and yours alone responsibility. In my opinion as a tutor and previous teacher, Steps 1 and 2 are a good project for those students in the technical classes, (Science, Math, Electrical, et al.) Many of them will be able to use the experience in their future careers.

Gather and Record Data
Organizing data is for the purpose of programming. All such sheets will not be usually be visible in the Master workbook.

You already have all the data gathered. The recording of data must be in simple lists or matrices as in my previous attachment and in the new SchoolTimingMatrix Sheet in this attachment. The TeachersNameList Sheet, as edited, is also an acceptable method, although the coder does not need any formatting except, always make the column headers Bold, Bottom or side Bordered and one size large font than the list font. This is a Best Practice for VBA


One Data zone you have not obviously considered is Student schedules: Matrices by Class of courses and students with Intersections showing Periods. See sheet in attachment. Do not consider saving space by limiting the number of data lists and matrix sheets. It is counter productive. Use as many as desired to organize the data effectively.



Design Data Display
In this attachment, you have two good examples of displaying Data, Teachers Workload and Daily school Timing. Note that Data Displays may not be in the Master Workbook, however blanks templates of each should be included therein.

This is where you let your imagination free. As you have been doing as you recorded data, create a display that has all that you need on it. A worksheet is a good design board, put all of them in one separate workbook. Only place Blank templates in the Master after all design work has been completed and Finalized.

Note that such Template sheets will not usually be visible in the Master workbook. Use as many as you need. When used, the Code will insert them into a new workbook as visible.

Note that some Data is best displayed on a UserForm.



Determine Rules and Constraints
This is where you tell the coder/programmer what must and can't be. These Restraints and Rules should/can be in a Word document with headings for each subject

Course Assignments:
No course can less than 10, nor more then 99 students
No course can less than 1 nor more than 10 teachers
No teacher can teach more than 3 courses in the same period
No student can enroll in 2 courses in the same period
Etc

Teachers Workload
The administration needs a complete sheets in a separate workbook, named ("***") with all teachers, all Classes, all courses for 4 weeks in the future, updated at start of Office each Friday, or the last workday before Friday.

All teachers need a sheet that shows only their workload, for two weeks in the future, updated as above.

The first three steps are usually interactive, that is, you will be moving between each step as thought occur, and errors and omissions become apparent.


Lay Out Data Input
In my mind this means designing the UserForms. However this design process can be done on a worksheet because it is merely an image of what you want it to look like. You can use worksheet controls to represent command buttons. Many professional Programming shops actually use printed images. It may be the most important concept to consider the Form User's workflow while designing the Form. It is best Practice to use a different form for each task. ie assigning a teacher to different courses, inputting a student's courses, et al. The programmer will make suggestions as to true Best Practice as he understand the UserForm possibilites and constraints.

It is best practice to design forms on the smallest computer monitor that will be used to view them, but it is not necessary.

This process is very interactive with step 2 and 3.

I hope this is of help for you in completing the Project in a timely and efficient manner.

ps: Yes the updated timetable is possible, but as you see, you will be doing most of the actual design of what you want and need. st


pps: I did some Best Practice Renaming on the UserForm and the Modules in the attached for your consideration. st

thinking of writing a thread about project design. need a place for notes.

Good Threads:
Business System Ideas - Advice Needed!
New project
Project : Maintaining Records and Preventing Duplicates!
Solved: Creating Index Items with Dynamic Hyperlinks to Cells in Workbook
Best practice for VBA coding - time to clean up a giant project
Passing values between userforms on different workbooks
VBA module / sub / function tree
Colour Entire Row Base on the Value of Two Other Cells
Help with Sentence Case

A Guided Tour Through The VBA IDE’s Options
Need VBA help to create copies of template and

Write down on paper, so you can see it often, in 30 words or less, exactly what you want this application do accomplish. I've done it for your App and I did it in 22 words. Don't count the first 3 words: "This Application will ..."

Obviously, you won't get into any detail in 30 words, but that is the idea. You need an overall goal.

Peter, here's the process I'm using to figure out how to write the code.

1) Determine what is needed

• Bottom Cell + 1 in J to start the Find from
• Bottom Cell in I
• MAX(Cell in I 30 days before Bottom of I OR Top cell in I)
• Const NumRecords = 1
• Const RecordWidth = 7 (columns)
• Relevant Range (From MAX to Bottom + 1)
• Allow for skipped dates and sections

2) Decide on the basic algorithm

• Iterate down Relevant Range (Cel)
• Find xlUp Relevant Range
• Stop when Cel.Address = Found.Address

3)Guess at future possibilities

• more than one months records.
• Auto-compress outdated section to totals
• Auto-insert new Day Section

4 Determine what knowledge is still needed (you tell me the answers.)

• Determine macro trigger (Manually Triggered From Menu?)