SOLVED

Task List that transfers to Calendar

%3CLINGO-SUB%20id%3D%22lingo-sub-2763702%22%20slang%3D%22en-US%22%3ETask%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763702%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20college%20currently%20and%20am%20needing%20to%20be%20as%20organized%20as%20possible%2C%20in%20order%20to%20maintain%20my%20grades.%20I%20have%20very%20minimal%20knowledge%20of%20VBA%2C%20but%20I%20have%20created%20some.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20try%20my%20best%20to%20explain%2C%20if%20you%20are%20able%20to%20help%20that%20would%20be%20awesome!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mendthefabric_0-1631982794185.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311304i267052502D56FB46%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mendthefabric_0-1631982794185.png%22%20alt%3D%22mendthefabric_0-1631982794185.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3ETask%20List%2C%20with%20Due%20Date%2C%20assignment%2C%20class%2C%20time%20due%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EVBA%20already%20installed%20into%20task%20list%20worksheet%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Excel.Range)%3C%2FP%3E%3CP%3EIf%20Target.Column%20%3D%201%20Then%3C%2FP%3E%3CP%3EDim%20lastRow%20As%20Long%20lastRow%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%3C%2FP%3E%3CP%3ERange(%22%24B%3A%24D%3AA%22%20%26amp%3B%20lastRow).Sort%20key1%3A%3DRange(%22A2%3AA%22%20%26amp%3B%20lastRow)%2C%20order1%3A%3DxlAscending%2C%20Header%3A%3DxlYes%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mendthefabric_1-1631982793810.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311305iC2D08A205FD9B711%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mendthefabric_1-1631982793810.png%22%20alt%3D%22mendthefabric_1-1631982793810.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3ECalender%20with%20Drop%20Down%20that%20updates%20automatically%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EWhat%20I%20Need%20Overall%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3E%3CEM%3Ea%20task%20list%20that%20automatically%20sorts%2C%20as%20I%20enter%20new%20data%2C%20by%20the%20due%20date%20and%20highlights%20the%20whole%20row%20if%20it%20contains%20the%20class%20name%20(VBA%20that%20is%20already%20on%20that%20list)%20(already%20done)%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EI%20need%20to%20take%20those%20assignments%20and%20enter%20them%20onto%20a%20monthly%20view%20calendar%2C%3CUL%3E%3CLI%3Esame%20document%20but%20different%20sheets.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3Ei%20need%20the%20assignments%20entered%20to%20highlight%20in%20the%20color%20of%20the%20class%2C%20automatically%26nbsp%3B%3C%2FLI%3E%3CLI%3Eif%20possible%3A%20as%20I%20enter%20data%2C%20automatically%20also%20enter%20and%20do%20all%20this%20code%20behind%20the%20scenes.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20hope%20this%20made%20sense%2C%20let%20me%20know%20if%20you%20can%20help%20with%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2763702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764072%22%20slang%3D%22en-US%22%3ERe%3A%20Task%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159200%22%20target%3D%22_blank%22%3E%40mendthefabric%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eas%20a%20variant%20(non%20vba)%20Power%20Pivot%20with%20slicers%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1632001696440.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311335iA01D9B762461CB31%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1632001696440.png%22%20alt%3D%22Yea_So_0-1632001696440.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764310%22%20slang%3D%22en-US%22%3ERe%3A%20Task%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764310%22%20slang%3D%22en-US%22%3EI%20appreciate%20the%20response%2C%20it%20isn't%20quite%20what%20I%20am%20looking%20for.%20I%20already%20have%20the%20VBA%20inserted%20into%20the%20list%20of%20assignments%20and%20I've%20done%20that%20so%20as%20I%20add%20to%20the%20list%20it%20automatically%20sorts%20it%20by%20date%20and%20it%20highlights%20it%20to%20the%20color%20of%20the%20class.%3CBR%20%2F%3E%3CBR%20%2F%3EBasically%20I%20need%20to%20know%20how%20to%20get%20that%20data%20to%20import%20to%20a%20calender%2C%20like%20the%20one%20shown%20in%20the%20picture%2C%20automatically%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764314%22%20slang%3D%22en-US%22%3ERe%3A%20Task%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159200%22%20target%3D%22_blank%22%3E%40mendthefabric%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20the%20calendar%2C%20do%20a%20vlookup%20against%20the%20task%20list%20similar%20to%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1632017549748.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311365i19116465CFC16404%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1632017549748.png%22%20alt%3D%22Yea_So_1-1632017549748.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1632017514498.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311364iCBAFC2F0D39FADD3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1632017514498.png%22%20alt%3D%22Yea_So_0-1632017514498.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764324%22%20slang%3D%22en-US%22%3ERe%3A%20Task%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159200%22%20target%3D%22_blank%22%3E%40mendthefabric%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20I%20suggested%20the%20Power%20Pivot%20is%20because%20you%20have%20to%20look%20at%20a%20lot%20of%20information%20in%20a%20tiny%20square%20in%20the%20calendar%2C%20whereas%20in%20the%20pivot%20table%20by%20clicking%20on%20the%20timeline%20or%20a%20specific%20day%20it%20will%20list%20all%20the%20classes%2C%20the%20assignments%2C%20the%20time%2C%20and%20you%20can%20filter%20out%20the%20done%20ones%20from%20the%20not%20done%20(submitted%20or%20not).%26nbsp%3B%20Its%20better%20for%20your%20eyes%20you%20don't%20want%20to%20have%20thick%20glasses%20by%20the%20time%20you%20graduate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2764327%22%20slang%3D%22en-US%22%3ERe%3A%20Task%20List%20that%20transfers%20to%20Calendar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2764327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159200%22%20target%3D%22_blank%22%3E%40mendthefabric%3C%2FA%3E%26nbsp%3BA%20couple%20of%20days%20ago%20another%20student%20asked%20a%20similar%20question.%20He%2Fshe%20chose%20to%20work%20with%20a%20typical%20Calendar%20template%20found%20on-line%2C%20with%20a%20monthly%20and%20a%20weekly%20view.%20Though%2C%20it%20only%20marked%20days%20that%20something%20was%20due%2C%20but%20it%20didn't%20specify%20what.%20I%20slightly%20modified%20that%20template%20to%20achieve%20just%20that%20and%26nbsp%3Bcould%20dump%20your%20assignments%20into%20the%20modified%20template.%20The%20calendar%20views%20were%20filled%20instantly.%20No%20VBA%20needed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPersonally%2C%20I%20would%20never%20use%20this%20kind%20of%20template.%20It's%20full%20with%20%22features%22%20that%20make%20it%20difficult%20to%20adapt%20to%20ones%20specific%20needs.%20So%2C%20use%20it%20as%20it%20is%20and%20don't%20try%20to%20tinker%20with%20it%20too%20much.%20Take%20it%2C%20or%20leave%20it%2C%20I%20would%20say.%3C%2FP%3E%3CP%3EOh%2C%20and%20since%20you%20tag%20your%20posting%20with%20%22Office365%22%2C%20I%20assume%20that%20your%20Excel%20version%20supports%20the%20FILTER%20function.%20If%20not%2C%20you%20may%20disregard%20the%20attached%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am in college currently and am needing to be as organized as possible, in order to maintain my grades. I have very minimal knowledge of VBA, but I have created some.

 

I will try my best to explain, if you are able to help that would be awesome!

mendthefabric_0-1631982794185.png

Task List, with Due Date, assignment, class, time due

VBA already installed into task list worksheet

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Then

Dim lastRow As Long lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("$B:$D:A" & lastRow).Sort key1:=Range("A2:A" & lastRow), order1:=xlAscending, Header:=xlYes

End If

End Sub

mendthefabric_1-1631982793810.png

Calender with Drop Down that updates automatically

What I Need Overall

  • a task list that automatically sorts, as I enter new data, by the due date and highlights the whole row if it contains the class name (VBA that is already on that list) (already done)
  • I need to take those assignments and enter them onto a monthly view calendar,
    • same document but different sheets.
  • i need the assignments entered to highlight in the color of the class, automatically 
  • if possible: as I enter data, automatically also enter and do all this code behind the scenes.

I hope this made sense, let me know if you can help with that.

9 Replies

@mendthefabric 

 

as a variant (non vba) Power Pivot with slicers

Yea_So_0-1632001696440.png

 

I appreciate the response, it isn't quite what I am looking for. I already have the VBA inserted into the list of assignments and I've done that so as I add to the list it automatically sorts it by date and it highlights it to the color of the class.

Basically I need to know how to get that data to import to a calender, like the one shown in the picture, automatically

@mendthefabric 

in the calendar, do a vlookup against the task list similar to this:

Yea_So_1-1632017549748.png

 

Yea_So_0-1632017514498.png

 

 

cheers

 

@mendthefabric 

 

The reason I suggested the Power Pivot is because you have to look at a lot of information in a tiny square in the calendar, whereas in the pivot table by clicking on the timeline or a specific day it will list all the classes, the assignments, the time, and you can filter out the done ones from the not done (submitted or not).  Its better for your eyes you don't want to have thick glasses by the time you graduate.

best response confirmed by mendthefabric (New Contributor)
Solution

@mendthefabric A couple of days ago another student asked a similar question. He/she chose to work with a typical Calendar template found on-line, with a monthly and a weekly view. Though, it only marked days that something was due, but it didn't specify what. I slightly modified that template to achieve just that and could dump your assignments into the modified template. The calendar views were filled instantly. No VBA needed. 

 

Personally, I would never use this kind of template. It's full with "features" that make it difficult to adapt to ones specific needs. So, use it as it is and don't try to tinker with it too much. Take it, or leave it, I would say.

Oh, and since you tag your posting with "Office365", I assume that your Excel version supports the FILTER function. If not, you may disregard the attached workbook.

 

 

@Riny_van_Eekelen 

 

wow nice work man! +1

one of the many experts on this community @mendthefabric 

This is actually perfect for what I need.
if I have another assignment, can i just add on to the bottom and it will sort it?

@mendthefabric Just add it at the bottom of the assignment table. It will expand automatically  No need to sort if for the Calendar to work. But if you want to sort the list, just push the little arrow at the top of the DUE DATE column and choose the sort option you want.

@Riny_van_Eekelen Great job, Riny. I knew @mendthefabric  would be able to find the help she was looking for by posting this publicly. (She had sent me a private message with the same original question, and I referred her here.)

 

And I also appreciate your comments vis-a-vis the templates. In general those so often have bells and whistles that get in the way of helping the very people they're intended to help.