• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA userform

L

Lilly

New Member
This is my vision and what I am trying to create. I will be as detailed as possible. Some one this information is a conversation between a programmer and myself:

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


I 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

Need to know

  • Everything is stored and saved to on the same network and in the same folder, The master may be moved to another folder but all workbooks will be saved in the same S: drive.
  • what triggers each transfer
    • I'll explain how the work is completed and what the workers and supervisors do: The supervisors will be assigning cases to each employee in the master log and enter that data on the master log in their corresponding employee's sheet/tab. The data entered will be in columns B-J.
    • Then once the employees see their assignments in their own workbook on sheet1 they will complete the case and put their updates to those specific cases on columns K-Q, that data entered needs to be communicated to the master log in the employee's corresponding sheet/tab.

  • At the end of the fiscal year the master log will either stay in the same S : Drive, moved to another folder, but in the same drive or deleted. The employees may save their logs, or may delete them.

  • At the beginning of the next fiscal year brand new workbooks are created from scratch usually. Which is why it would be great to know this code to be able to make multiple workbooks in the future.


Code requirements
  • Yes, all employee workbooks are named after the employee and they all have the exact same listed columns and will be entering similar data.

  • Yes, we also need to be able to remove or add new employees to the master log and create new employee sheets and workbooks just in case employees leave sometime throughout the fiscal year.

As a "Project Designer," it was suggested in 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



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

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.


http://www.vbaexpress.com/forum/sho...-code-to-transfer-data-to-different-workbooks
 
These are the exact steps the employee's and supervisors take:

The Employee:

Once cases are assigned, the worker will go to their log and usually just clear the cases for documents and narrate any documents that are still needed. Once they do that and place comments next to each assignment, they actually send a request form which triggers the first response/action of the case. The employee will then change the status cell in the column titled "status" (which has the dispositions in a drop down) and select 1st 10 day as well as enter the date in the lst 10 day column. This repeats for the 2nd 10 day status.

If all documents are received and the employee can approve the case the status will then be approved. Each case can potentially be on the employee's log with the status changed up to 5 times, but usually 4, so the employee needs to be able to monitor the cases and do multiple status changes and change the comments in the comments column as well. Assignments are 100% completed once a case is approved or denied, however an employee must still be able to make any changes to those cells which the assignments are completed due to changes sometimes occuring.

Everytime an employee touches an assignment and does some sort of work on it, they must enter an action date in the action date column. Data is entered in the zip code usually at whatever time the employee feels to enter it, but it must be entered by the time the assignment is completed. The data for the bank column is entered once the assignment is completed and may change, may not.

The Supervisor:

The supervisors periodically will assign work to the employees as they receive them. When a supervisor is given the assignments, they will either spread them out to each employee or just assign all those cases to 1 employee. Once in the employee's tab they enter the employee's worker number, their name, the case ID, and sometimes there are two with two columns, the applications date, the date assigned to the employee and the case first and last name.

Tricky part is when supervisors need to reassign cases. For example an employee is not doing very well with their assignments and they are really behind, supervisors may take the assignments that are still in pending status (usually the 1st and 2nd 10 day cases) and give them to other employees to complete. Those cases that are chosen are usually at random. Another example is if an employee leaves to another department and their cases need to be reassigned to the current employees. These are really the only things supervisors will be doing to the master log.

The thing that supervisors need and is very important is the tracking of the numbers, like the tracking tool I have in the Master log in the first tab. They need to be able to monitor every employee's numbers in real time, or at least once an employee saves their workbook and it updates the master's stats.

The one thing I don't have one there and would love to incorporate in this workbook is a tracking tool that can track the status's of the assignments by months. For example, it is important for us to monitor how quickly assignments with application dates in the month of January are completed. So the tracking tool I have now only tracks the overall assignment load of all the employees, but I would like a tracking tool that can also do it by application month's, if that makes sense.
 
You must log in or register to reply here.
Back
Top