Post Holding thread for my own use

[SamT]

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 .

[SamT]

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.

[SamT]

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.

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.

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.

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.

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

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.

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.

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.

[SamT]

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 ) 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.

[SamT]

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.

[SamT]

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

[SamT]

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.