PDA

View Full Version : VBA code to transfer data to different workbooks



Pancakes1032
10-18-2014, 04:36 PM
Hello, I really hope someone can help me out, I've been trying to figure this out for days. I'm really new to this and am trying to understand and learn but also am in need of getting this to work asap. Below is what I am trying to do.

I have Workbook 1 (Master log) needs to be able to send data from sheet 2 columns B through J to multiple workbooks (Workbook 2/employee 1's log) on sheet 1 of workbook 2 in those same columns (B-J) and I mean like 20 different ones workbooks eventually. There will be multiple Employee logs that will send data to the Master log according to specific sheets. For example the Master log will have 20 sheets, one for each employee, so employee 1's log (which we will reference as workbook 2) will send data to the Master log sheet 2 and Employee 2's log will send data to the Master log sheet 3. Then Workbook 2 (and all other employee workbooks) needs to be able to send data from sheet 1 Columns K through Q to Workbook 1 (Master log) to their specific sheets (example, employee 1 will send data to sheet 2 and employee 2 will send data to sheet 3) in columns K through Q as well.

I also need a code to be able to get a code that can do the same type of thing but for rows instead of columns.

Also I am pretty new to this and don't speak code yet. So having these---> () blank and expecting me to know what to put in there won't help because I genuinely don't know if something goes in there or not and I wouldn't know what to put so please try to dumb it down if possible.
Thank you a TON if you can help me can help me!

I have also created dummie workbooks so that you can see what I am trying to do as an example. I also have another big help question, but for right now this is definitely the most important thing to get to work for me.

SamT
10-19-2014, 07:12 AM
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

Book Master Log, Sheet Employee(n).Name

to Book Employee(n).Name, Sheet Employee(n).Name


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
10-19-2014, 07:21 AM
If any new data in the workbooks must be manually entered, I strongly suggest that you use Custom UserForms to perform all actions required.

For info on UserForms, open the VBA Editor (VBE,) and insure that the Project Explorer is visible, (Press F2.)

Right click on ThisWorkbook in the Project Explorer window, and select Insert UserForm.

Click the new UserForm and press F1. In XP you would use the See Also option of this Help window

Pancakes1032
10-19-2014, 09:16 AM
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.


Yes all of this is on point


You need to perform


Transfer data from Certain Ranges in

Book Master Log, Sheet Employee(n).Name

to Book Employee(n).Name, Sheet Employee(n).Name


Book Employee(n).Name

to Book Master Log, Sheet Employee(n).Name
Yes this is all on point




We need to know


Where everything is stored or saved to.
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

Click a button,
Change Worksheets in master Log
Open a Employee(n).Name Book
Use a Menu selected Macro
Some other event or selection
I'm not 100% sure what you mean by this but 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.


What happens to books Master Log and Employee(n).Name at the end of the fiscal period
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.
What happens to books Master Log and Employee(n).Name at the beginning of the next fiscal period
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

All Items with Name = Employee(n).Name are identically named
Yes, all employee workbooks are named after the employee and they all have the exact same listed columns and will be entering similar data.
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 .
Yes, we also 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 through the fiscal year.




Thank you SO much if you can help me.

Pancakes1032
10-19-2014, 09:21 AM
Yes there is constantly new data being manually entered, especially when the supervisors enter new assignments and when employees enter the data updates to those new assignments. I have its where C:\Users\Dummy\Desktop\[Play Employee.xlsm]Employee 1'!D4 I have this formula in the individual cells but this isn't efficient enough, I would like the entire columns to communicate not individual cells.

I also did the userforms but I have no idea what it does.

Thank you again.

SamT
10-19-2014, 02:15 PM
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
10-19-2014, 02:41 PM
what triggers each transfer


I'm not 100% sure what you mean by this but I'll explain how the work is completed and what the workers and supervisors do:


That explanation is what I needed

A VBA Procedure, (a hand written Macro) won't run by itself, it must be "Triggered." In the Scenario I just posted, the main Procedures run when the workbooks are Opened. Thereafter, others are triggered when the User performs certain actions in the UserForms.

I would change that Scenario in a couple of ways.

The Employee's .xlsm workbooks would all be the same and would only have the code and UserForms. They would even have the same name and would be stored on the user's computer(s). Code can determine the Employee Name from the Computers Logon Screen. This would allow one user to log on to another's computer and continue working on his/her assignments.

The data only files (.xlsx Assignments workbooks) that were passed back and forth with the supervisors would be identical to the "Play Employee.xlsx" book that you originally uploaded. I strongly suggest that all completed assignments be removed by the supervisor as soon as the data has been recorded in the MasterLog book for data security. It appears that you are in a high security workplace.

Pancakes1032
10-19-2014, 03:44 PM
I think I see what you're saying but I don't completely 100% understand it. 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.

I really like the idea of the workbooks being attached to the employee's windows login so that they can log into any computer, 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.

What I don't think is there being so many folders, it kind of seems like too many workbooks and folders being created. At least that is the way I understood what you were saying, with regards to the folder assignments and folder returned. Unless I am just not understanding completely what you're saying.

The supervisors don't technically need to be able to view the employee's workbook or make changes. 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.

I've attached the workbooks 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 and 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 formulas start on cells B2 in the employee's log and K2 in the master log.

Hopefully this helps clarify what I am trying to achieve.

SamT
10-20-2014, 07:42 AM
I think that the way you re envisioning the system is that

Each Employee has a workbook, (so that they can't see each other's cases?)

They manually enter data into each cell on each row.


All Supervisors share one master workbook, (So they can all monitor the master log sheet?)

They manually enter data into each cell in each row.
The VBA in the Master book transfers data from the Master into all the Employee books.


At some point in time the Employee books have to be saved back a folder that the supervisors and employees all have access to.

Randomly thru the workday
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.



Any given workbook can only be opened for data manipulation by one User at a time.
With one Master Log book

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



If using only one folder to store all workbooks

All Employee books must be saved, (and no assignments worked on) by a set schedule
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
10-20-2014, 07:55 AM
Stop thinking about how your Master Log workbook should work and instead think about what the system must accomplish for your office.

An experienced Project Designer can quickly envision the outline of a system the will do what any office needs done.

Imagine how long it would take you to teach me to do your job as well as you can do it?

What a Project Designer is good at is to take the businesses requirements and work and data flows and creating a system that is optimized for those. A good PD also has a good knowledge of computer Users in general. This is important because the Users are the heart of any system.

Pancakes1032
10-20-2014, 08:18 AM
You addressed all of my concerns. I understand that only one supervisor can be on at a time to enter updates and adjust the master log and that is fine. Everything else seems to look good. Thank you so much for your help!

SamT
10-20-2014, 12:39 PM
I understand that only one supervisor can be on at a time to enter updates and adjust the master log and that is fine.
Not quite true. Notice that in my last post in re Supervisor's Viewpoint, I did not mention any wait time. That was deliberate.

The way VBA does things, it will use a Read Only copy of Master Log to enter all data, then check if Master Log is Locked, if not then open master Log, update it from the copy and close it. that entire process will only take a few seconds. The supervisors will rarely experience any delays let alone actually notice them.

Pancakes1032
10-20-2014, 04:58 PM
Oh okay, well then that works perfect!

SamT
10-20-2014, 05:24 PM
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.

Pancakes1032
10-21-2014, 07:35 AM
I actually know the exact steps both would 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.



Also another issue that I am currently having, and I'm not sure if this will be a problem with the design you are creating, is that because the individual cells have reference formulas the search function, sort function, and copy of the text doesn't work because the cells have a formula and not the actual text and that can really impact an employee's work. If we can get that function to work and still be able to assign and monitor the assignments correctly that would be great.

Hope this all helps. Thank you :)

SamT
10-21-2014, 05:23 PM
About "The Employee:"

First Paragraph:
I am looking at Play Employee.xlsx right now and I don't see any column for any 10 Day status.

You said, "the worker will go to their log and usually just clear the cases for documents and narrate any documents that are still needed." What do you exactly mean by "Their log?" No Document columns are in the Play Employees workbook. By "Narrate" do you mean they are speaking?

You said, "select 1st 10 day as well as enter the date in the lst 10 day column." That is two actions and implies that there are some multiple of two more columns, which I don't see.

Second Paragraph:
"however an employee must still be able to make any changes to those cells which the assignments are completed due to changes sometimes occurring." Are those changes made by the Supervisor and that assignment resent to the Employee. IOW, Who is the first to know about the changes, the Supervisor or the Employee?

Third Paragraph:
"Every time an employee touches an assignment and does some sort of work on it, they must enter an action date in the action date column" Automatic via VBA code. Also that column changes every day an action is made.

In Conclusion:
From what you said, it looks like the only interaction with the UserForm that an Employee needs is to see the Assignments, select one, change the Status, and select the Zip code and Bank Data. All other functions can be handled by VBA Code.

It should be assumed that ancillary functions like save and get new assignments, etc. are included.


About "The Supervisor:"

Second Paragraph:
Good to know about that. We will need to encode cut and paste abilities or some other method to transfer that data.

Third Paragraph:
We can add to that UserForm a section that displays that information for the particular Employee that the Supervisor is working on at the moment.

Fourth Paragraph:
"but I would like a tracking tool that can also do it by application month's, if that makes sense." All Employees by month or just a monthly summary, ie, one line per month for many months. Or Both. In each case it just means one more sheet in the Master log and a little bit of code.



Another Issue Paragraph:
The functionality of all formulas will be moved to the VBA code



You are starting to have to keep track of a lot of data. If we can keep all the functionality we have discussed, would you mind if we very slightly change the way in which that data is organized?

What I am suggesting is that all Employee's data is permanently stored in an Employee book like the current Play Employee.xlsx.

Employees come and go, quite often in different Fiscal periods. A sheet for them would be created in Master Employee Log.xlsx book when they start, and would be removed at the end of the fiscal period that they leave. We would still save all data every fiscal period.

Master Log.xlsx would only have the Master Log sheet, the Reference sheet, and the sheets mentioned in About Supervisors, para 4 above.

I believe this would make the code easier to write and to maintain.


In regards to my post #14
We really do need to know the exact order in which they perform every step in the process. :banghead:

Also, how do the employees know which documents are cleared and how they know which ones are still needed?

How do the Supervisors receive new assignments?

While creating this system will probably take less time than I have already spent on it, (around 14 hours, so far,) nobody want us to deliver it and then discover that it is not right and we have to do it all over again. :crying:

Pancakes1032
10-21-2014, 09:05 PM
About "The Employee:"

First Paragraph:
I am looking at Play Employee.xlsx right now and I don't see any column for any 10 Day status.

I apologize, I meant the 1st response column, in this column the employee will be entering dates, as well as the 2nd response column and the action date column.

The supervisor enters dates on the application date column and the date assigned column.

You said, "the worker will go to their log and usually just clear the cases for documents and narrate any documents that are still needed." What do you exactly mean by "Their log?" No Document columns are in the Play Employees workbook. By "Narrate" do you mean they are speaking?

Again, I'm sorry I was speaking in terms we use in our business. The employee's log is essentially their person workbook (ex. Play Employee) each employee has their own and we usually refer to them as logs.

Also by narrate I mean that the employee will enter their remarks on the assignment they touch in the comments column, this column will be updated with the employee's comments/remarks, in text form, every time they touch an assignment.

You said, "select 1st 10 day as well as enter the date in the lst 10 day column." That is two actions and implies that there are some multiple of two more columns, which I don't see.

Correct, it is essentially two actions. If they need to send the 1st request for a response to the client, they will first enter the date which the response by the client is due in the 1st response column which is column K and they will select 1st response or first 10 day in the status's drop down list. If I don't have that list set up in the status column, I apologize, but it is normally there and I can upload an example of what I am talking about in the morning.

The worker will do this series of steps twice, once for the 1st request for a response (column K) and status update (column M) and then repeat the steps when they send the 2nd request for a response (column L) and change the status (column M) to be 2nd response.

Second Paragraph:
"however an employee must still be able to make any changes to those cells which the assignments are completed due to changes sometimes occurring." Are those changes made by the Supervisor and that assignment resent to the Employee. IOW, Who is the first to know about the changes, the Supervisor or the Employee?

Changes to the status of an assignment are always made by an employee, the employee is usually always the first one to know, sometimes a supervisor may be aware of it, but he or she almost always has the employee process the assignment's new status. Status (column M) values can be unprocessed, (empty,) "Approved," "1st response," "2nd response," and Denied." Changes happen periodically usually through a 45 sometimes 60 day process. That is usually the time frame that is takes for an employee to actually complete the process of an assignment.

Third Paragraph:
"Every time an employee touches an assignment and does some sort of work on it, they must enter an action date in the action date column" Automatic via VBA code. Also that column changes every day an action is made.

Oh awesome, that'll save time!

In Conclusion:
From what you said, it looks like the only interaction with the UserForm that an Employee needs is to see the Assignments, select one, change the Status, and select the Zip code and Bank Data. All other functions can be handled by VBA Code.

It should be assumed that ancillary functions like save and get new assignments, etc. are included.

Yes that is essentially all the employees do, they do enter dates in columns (K and L) however we can just make the an automatic thing as well. Any time the employee changes the status to 1st response it means that 10 days from that action the client's documents are due. Same thing for the 2nd response. So either the employee can enter that date or it can auto populate in those columns.


About "The Supervisor:"

Second Paragraph:
Good to know about that. We will need to encode cut and paste abilities or some other method to transfer that data.

Third Paragraph:
We can add to that UserForm a section that displays that information for the particular Employee that the Supervisor is working on at the moment.

Fourth Paragraph:
"but I would like a tracking tool that can also do it by application month's, if that makes sense." All Employees by month or just a monthly summary, ie, one line per month for many months. Or Both. In each case it just means one more sheet in the Master log and a little bit of code.

Oh no that is not what I meant at all. Okay so Employees get assignments every month (usually corresponds to the app date in column D). The supervisors would like the be able to track the progress of the assignments of every employee by the month of applications, (Column D.) For example in the Play Master log, the first tab has a tracking tool that tracks all the approvals, denials, unprocessed, 1st responses, and so forth for all employees. What would be great is to have a tracking tool just like that but tracks the status by the application month. Hopefully that makes more sense.



Another Issue Paragraph:
The functionality of all formulas will be moved to the VBA code



You are starting to have to keep track of a lot of data. If we can keep all the functionality we have discussed, would you mind if we very slightly change the way in which that data is organized?



What I am suggesting is that all Employee's data is permanently stored in an Employee book like the current Play Employee.xlsx.

Employees come and go, quite often in different Fiscal periods. A sheet for them would be created in Master Employee Log.xlsx book when they start, and would be removed at the end of the fiscal period that they leave. We would still save all data every fiscal period.

Master Log.xlsx would only have the Master Log sheet, the Reference sheet, and the sheets mentioned in About Supervisors, para 4 above.

I believe this would make the code easier to write and to maintain.

I think I see what you're saying and it looks like this would make things much easier. Keep in mind the master workbook does need the tracking tool to be able to monitor the progress of the cases, like the one in the Play Master.

In regards to my post #14
We really do need to know the exact order in which they perform every step in the process. :banghead:

I really wish that there was an exact order but in all honesty every employee works differently. The steps that I mentioned above are the steps that every employee takes but it may not be in that order. For example an employee may just approve an assignment right away and not need to do the 1st and 2nd response steps. But they all touch the same columns.

Also, how do the employees know which documents are cleared and how they know which ones are still needed?

The employees know the business process and eligibility for their assignments. We have other programs where they can see those documents. We don't need to incorporate any of those programs for these workbooks. They are managed by another agency.

How do the Supervisors receive new assignments?

They get them from a different department and program there is also no way to incorporate that process in these workbooks, at least not right now.

While creating this system will probably take less time than I have already spent on it, (around 14 hours, so far,) nobody want us to deliver it and then discover that it is not right and we have to do it all over again. :crying:

I understand and am so terribly sorry that it has taken this long to get the details right :(

SamT
10-22-2014, 07:02 AM
I understand and am so terribly sorry that it has taken this long to get the details right :(
:rofl2:

I come here to help people with their VBA code.

For fun. :dance:

If it stops being fun, I will ask someone else to step in.

Pancakes1032
10-22-2014, 08:38 AM
hahaha okay great 1 :)

Let me know if there is anything else you need.

SamT
10-22-2014, 11:04 AM
Wow, you must have been tired last night. This is the first time I have seen you mangle the American language like that. You are the best communicator I have dealt on such a complex problem. You have almost always limited your vocabulary to the vocabulary of the workbooks you uploaded.

If you go back and look at your last post, you will see a lot of purple words.Those are my edits of your post. I corrected some typos, edited some sentence structures, and added some of my best guesses as to what you were trying to tell me. I already see that I was factually wrong about the drop down list in the Status column. Please check it out and tell me where else I was wrong and what it should be.

Can you show us all the dropdown lists. IMO, the best way is to create them in an excel sheet, then copy that table and just paste it into your post. I am running XL 2003, so I can't see them in .xlsx workbooks.

Here is an image of a conceptual Employee's User Form
Yellow background indicates that it is locked from the User and auto-completed by code
Green is instructional
Black borders indicate a Label (Like a Column header)

I already see some errors and omissions in the Command Buttons. Please tell us any suggestions or wants that you have.

Click Image for larger size

Pancakes1032
10-22-2014, 11:45 AM
hahahaha! Thank you for the compliment. I was extremely tired last night; it's been a crazy couple of weeks :(

Wow your design looks amazing! I had no idea excel could even do that.

Here are a couple things I see that are missing that we definitely need on our logs:

The employees need to be able to actually see the application dates, case name, and case ID, which is usually the info that the supervisors enter, but the employee does need to be able to see them.

What would also be great is that the employees usually will get told by the supervisors to work on only 1st response assignments for the day, or to work on only pending BRE assignments, and so forth. A function where the employee's will be able to search and/or filter those specific assignments and possibly see all of them would be great.

Thank you!

SamT
10-22-2014, 12:14 PM
The employees need to be able to actually see the ...info that the supervisors enter,

No problem.

In the Multi-Column dropdown list of pending applications, what are all the columns that an employee may have to use the determine which application to work on. Status, of course, but any others. We want that dropdown list to be as narrow as possible, but no narrower than needed.


A function where the employee's will be able to search and/or filter those specific assignments and possibly see all of them would be great. Can Do.

SamT
10-22-2014, 12:16 PM
BTW, I'll be staying between Stockton and San Jose during the last half of November . :beerchug: ?

Pancakes1032
10-22-2014, 12:52 PM
I went ahead and added some pictures and explaination of things that are needed in the attachment.

Would it be possible to show me what it would look like if the employee selects the all 1st response assignments in the drop down? I just would like to know if it would pop up as multiples of the 2nd half of your example, or just the previous comments, and new comments for the specific highlighted assignment?

That's great, not sure if you've been to California before, but the weather has been great lately! I don't live anywhere near there though :(

SamT
10-22-2014, 07:34 PM
Born and raised in Niland, 3 miles east of the Salton Sea.

Lived in San Diego area, greater Los Angeles, Simi Valley, Oxnard, Ventura, and who doesn't know Lebec. In SoCal

I'll get back tomorrow on the UserForm


In the Multi-Column dropdown list of pending applications, what are all the columns that an employee may have to use the determine which application to work on? Status, of course, but any others

Pancakes1032
10-23-2014, 07:15 AM
Wow that's a lot of places. I'm born and raised San Diego, I love it, it's really hard to leave haha

SamT
10-23-2014, 11:34 AM
Still no code in it, Just for your approval of layout and to make sure we know all the stuff you want one it. And don't want :D


12431

Pancakes1032
10-23-2014, 12:35 PM
That looks great! I just need to make sure that for the drop down list for the banked, that I am able to remove or add new bank codes to that drop down, same thing for the zip code. Other then that it looks great. What would the supervisor userform form look like?

SamT
10-23-2014, 01:42 PM
All Macro enabled workbooks have a reference sheet that holds all lists for all that kind of data. It appears in dropdowns in the same order as the list.

One of the reasons that original master books are all kept in one location is that when you need to update a list, you only update it there, then distribute copies to every one.

Did you know that VBA for Excel can import data from Word, Notepad, Outlook, Power Point, Internet Explorer, and many, many other sources. Tell us more about the data the Supervisor use.

The Supervisor's UserForm mockup will take a little while.

Pancakes1032
10-23-2014, 02:17 PM
Sounds good!

I had no idea that it does that! That is amazing! Okay so this is how the supervisors receive the assignments that they end up assigning to the employees. The assignments are extracted from the management report, the report is online, but it's basically another excel workbook. I have all the information about that workbook so I would be able to link it to the master workbook and we could possibly extract that data, the only problem is that management report workbook is not something I have the power to edit (at least not for now) and is formatted to be a read only (always). I'm pretty sure they have some restrictions set on it too.

Another issue is that the workbook is not formatted the way you are designing the new workbooks, the columns are a little different, but I do know which columns have which data we would need. Another issue is that there would need to be a code that can pull the specific data that is on that workbook because it's not just my office's information that is on there. I know the steps to get the information though: Once management report is opened there is a drop down list (formatted similar to how you have the drop down list to choose an assignment) where we choose our specific office location, once clicked, we then click the extract button and it pulls all of the assignments for our office. However we only process the assignments that are in a specific bank, so we almost always filter that worksheet to the specific bank we need and that is where all of our specific assignments are.

Hopefully that made sense :confused2

SamT
10-24-2014, 08:49 AM
we then click the extract button and it pulls all of the assignments for our office.

In programming parlance, "Extract" can mean "Import," which means to read data from another application, (like a Word Document) and write it into the requesting application, (like a PowerPoint Presentation.) Oh. Didn't I mention that all Microsoft Office Applications can import (and export) data just like Excel? My Bad :D

However, I get this feeling that it doesn't quite mean the same in your office lingo, especially since the Extract button is in the Management Report Workbook.

After the "Extract" button is clicked, are the new assignments then present inside a Play_Master type workbook?

I bet not. :)




Open a Management Report book, "Save As" it to your desktop. With that workbook, Delete all but six Assignment Rows. Obfuscate any personal identifying data. "Save As" that book by adding "Play" to the name. Do NOT change the name in any other way.

Upload the "Play +" Management Report workbook for us.

Pancakes1032
10-24-2014, 12:53 PM
I actually had these screen shots done prior to seeing this response, so hopefully this is a good enough idea of what you need in order to get this workbook to communicate to the supervisors/master log workbook. I also added any formulas or codes that are currently being used on that log that I can see.

I don't want you to feel pressured at all and know that what you have opened my eyes to is amazing, but our fiscal year and the workbooks that we have been working towards starts 11/10/2014. I probably should have mentioned that earlier, but I had no idea excel could do what it does or that it was so technically complex.

I want you to know how excited, motivated, and appreciative of the help that you have been giving me. I have really became interested in this type of computer work and would really love to learn how do this this stuff myself one day. I'm hoping that by reading your code I can learn little by little and of course take classes.

Again, thank you immensely!

SamT
10-24-2014, 03:55 PM
From the information available in that Document, If we know the location of the files we can automatically extract all the assignments for your office (if we know the ID of your office as seen in Column I) from the latest New Daily Pending Applications Summary.

Here is a very rough concept of what a Supervisor will see when he/she opens their book. How Ruff is it? The "Do Something Else" Button should be named "Get Pending Assignments" and be first thing on the list. Each button opens a different Form. For instance Clicking the "Complete Assignments Like An Employee" button (Stupid name or what?) opens a Form exactly like the Employee's User Forms, except it has a way to select any employees Assignment book. I haven' Even started any of those Forms.
12443

The Attached is another Concept Form. The only Button in it that actually does anything is the Bright Red one on the "Work On Assignments Tab. Play with the tabs and the controls on the aforenamed Tab. Try to edit the work book and save it :)


but our fiscal year and the workbooks that we have been working towards starts 11/10/2014

Uh oh. I won't be around long enough to meet that deadline and I don't think any or our other (unpaid) volunteers want to take on this big of a project. I am going to refer you to Jacob Hilderbrand (http://www.vbaexpress.com/forum/member.php?135-Jacob-Hilderbrand) at our Paid Services Branch (http://www.vbaexpress.com/consulting.html). He is the owner of this forum and is still a very active VBA professional. I will also send him a message to read this thread and will send him a package of what I have already accomplished.

Until he takes this thread over, I will continue to help you.

Dang it! This was fun!

Pancakes1032
10-24-2014, 05:03 PM
My heart literally sank when I read "I won't be around long enough to meet that deadline" :crying::boohoo


What you have really looks great by the way. I wish I could see the code of the cencepts form so I could play with it and learn, but I think it's locked.

SamT
10-24-2014, 05:56 PM
but I think it's locked.
Nope. That little trick is the only code in the book. All 12 lines of it. And it is a real hack, I must remove it before I send it to Jacob.

Unless an Employee has the need to see all his/her assignments at once, it is a good security measure. Remember each workbook has its own Reference sheet that should only be changed by an Administrator with the Administartors UserForms.

Jacob has my phone number and his Partners?... Associates? ... Hmmmn... Gang? Yeah, that's it, gang, have offices in San Diego, so ... who knows whats the future holds for us. I sure don't.

Check your notifications at the top of the page for a PM.

SamT
10-24-2014, 07:21 PM
I have been trying to so what I said in the PM and it's just too hard right now. I am just going top delete the attachments.

I think that Jacob can easily get your system basically usable and saving data for when it is complete before the deadline. It might not be complete, but it will be working enough that no data will be lost and all features can be used retrospectively.

IOW, we can seamlessly implement the project in two phases, one absolutely before Fiscal End and one after. the first will save all data in a mnner that it will be instantly usable when the second phase rolls out.

Or not. But I have a lot of faith in Jacob and his "Gang," any one of whom is way better at this then I. See our home page at vbaexpress.com

Do a quick web search for "vba express, vbaexpress, vbax group" You can ignore the ones for Linux, those are a program name.

Pancakes1032
10-24-2014, 08:07 PM
Well when you call them a "gang" a little red flag pops up... haha

Okay well I guess we can just keep going from here until someone else steps in. Don't worry about the screen shots, I know the path so I can just discuss that with Jacob as we go. Implementing the system in phases can work, probably would be better that way so that if there is an initial issue it can be addressed.