Forum Discussion
Task List that transfers to Calendar
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!
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
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.
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.
15 Replies
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.
- I wonder is there a way of adding a year view added to the template that works in the same way as the month view and week view?
- I convert excel sheet to .ics and import .ics file to android smartphone calendar which has both month and year view
Is it possible to add the Class to this as well? So it might read
CLASS: Assignment
on the specific date in both month and week views? I have not programmed anything in years, and tried to add it in and am getting a syntax error.
criley03 Easiest would be to expand the table with a MERGED column where you concatenate the Assignment and Class columns into one. Then, in the Month and week views use Find&Replace.
Find what?
[[DESCRIPTION]:[DESCRIPTION]]
Replace with?
[[MERGED]:[MERGED]]
See attached.
- This is awesome and exactly what I have been looking for, however when I enable editing, none of the assignments are filling in. I think it is because you are using a function that my version of Excel (2016) doesn't support but I am unable to see what that is. In the Monthly view, here is a copy/paste of one of the formulas. Please note the _xlfn. Would you be able to tell me what that is supposed to be?
Thank you!
=IFERROR(_xlfn.TEXTJOIN(REPT(CHAR(10),2),TRUE,_xlfn._xlws.FILTER(Assignments[[DESCRIPTION]:[DESCRIPTION]],Assignments[[DUE DATE1]:[DUE DATE1]]=(DATEVALUE(G14&"-"&MoMonth&"-"&MoYear)),"-")),"")ashleycole24 Indeed. The _xlfn prefix indicates that the TEXTJOIN and FILTER functions that are not available in 2016.
- 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
