Background:
Project management tools are a dime a dozen. But sometimes simple solutions are enough, especially if they combine Excel VBA with Python, so you don’t have to use a sledgehammer to crack a nut (or spend a fortune). One of the main components of a project management tool is tracking project milestones in terms of their assigned capacities and fulfillment. In addition, the input and evaluation should be as user-friendly as possible. In this article we will see how a simple self-programmed solution to this can look like.
Solution:
First, let’s take a look at the Project template. After opening the file, we are informed that we are currently the only user who has the file in progress.
This functionality has been achieved through VBA (ThisWorkbook).
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Range("r4").Value = Date
If Range("G2").Value = "" Then
MsgBox ("Hello " & Application.UserName & "! This file is currently edited only by you.")
Range("G2").Value = Application.UserName & " works on this file since " & Time & "."
Else
MsgBox ("Mrs/Mr " & ActiveSheet.Cells(2, 7))
End If
Application.ScreenUpdating = True
End Sub
The reason for this feature is to facilitate collaboration with colleagues: since this template is not (yet) a multi-client database application, several users cannot work on this file at the same time. An outlook for this requirement will be mentioned in the end of this article.
First, let’s take a look at the general overview.
As we can see, the template is basically divided into three areas.
The first is the upper area with the function buttons.
Then there is the project overview on the left side.
And finally, the corresponding Gantt-chart on the right side.
Project overview
This area lists the individual milestones including the project tasks. Besides a few free text comment columns, a project manager can be selected in column H with a search form. Whenever you click into column H a form pops up which enables string search for convenient search and selection:
This has already been explained extensively in another article. Therefore, the functionality is not discussed in detail here.
Column P contains the possibility to store a prioritization. By double-clicking in this cell, a bar is added so that a maximum of four priority bars can be stored.
This is the highest prioritization. Double-clicking again or deleting the cell removes the priority bar, and the priority levels can be increased again from 0.
Another interesting form is in column Q and R. This represents the time window in which the subtask should be started and finished. When you click in this cell, a calendar appears. A great thanks to H. Ziplies, who originally created this awesome calendar form.
The VBA code behind the calendar form consists mainly of these components:
Private Sub UserForm_Activate() ' start instance
For InI = 1900 To 2100
Cbo_Jahr.AddItem InI ' Year input
Next InI
Cbo_Jahr.Tag = 1 ' so that Create at Change is not triggered
Cbo_Jahr = Year(Date) ' Display of the current year
Cbo_Jahr.Tag = "" ' so that create is triggered on change
Lbl_Datum = Format(Date, "MMMM YYYY") ' show current date show month and year
For InI = 1 To 12 ' enter months
Cbo_Monat.AddItem Format(DateSerial(Year(Date), _
InI, 1), "MMMM") ' enter month
Next InI
Cbo_Monat = Format(Date, "MMMM") ' Display of the current month, the calendar is created
End Sub
Private Sub Cbo_Year_Change() ' Event when changing the selection
If Cbo_Jahr.Tag = "" Then
Erstellen DateSerial(Cbo_Jahr, _
Cbo_Monat.ListIndex + 1, 1) ' create calendar
End If
End Sub
Private Sub Cbo_Month_Change() ' Event when changing the selection
If Cbo_Monat.Tag = "" Then
Erstellen DateSerial(Cbo_Jahr, _
Cbo_Monat.ListIndex + 1, 1) ' create calendar
End If
End Sub
Private Sub Scb_Month_Change() ' Change event
If Scb_Monat.Tag = "" Then
Erstellen DateSerial(1900 + Val(Scb_Monat / 12), Scb_Monat - _
Val(Scb_Monat / 12) * 12, 1) ' create calendar
End If
End Sub
One of the outstanding features of this calendar is the highlighting of specific dates:
Public holidays are highlighted in green, the current day in yellow and weekends in red. In this case, the holidays are set for Germany, but can be customized for any country:
Function PublicHoliday(Datum As Date) As String
Dim J As Integer, D As Integer
Dim O As Date
J = Year(Datum)
D = (((255–11 * (J Mod 19)) - 21) Mod 30) + 21 ' Easter calculation
O = DateSerial(J, 3, 1) + D + (D > 48) + 6 - _
((J + J 4 + D + (D > 48) + 1) Mod 7)
Select Case Datum
Case DateSerial(J, 1, 1)
PublicHoliday = "Neujahr"
Case DateAdd("D", -2, O)
PublicHoliday = "Karfreitag"
Case O
PublicHoliday = "Ostersonntag"
Case DateAdd("D", 1, O)
PublicHoliday = "Ostermontag"
Case DateSerial(J, 5, 1)
PublicHoliday = "Erster Mai"
Case DateAdd("D", 39, O)
PublicHoliday = "Christi Himmelfahrt"
Case DateAdd("D", 49, O)
PublicHoliday = "Pfingstsonntag"
Case DateAdd("D", 50, O)
PublicHoliday = "Pfingstmontag"
Case DateSerial(J, 10, 3)
If Datum > "01.01.1990" Then
PublicHoliday = "Deutsche Einheit"
Else
PublicHoliday = ""
End If
Case DateSerial(J, 12, 25)
PublicHoliday = "1. Weihnachtstag"
Case DateSerial(J, 12, 26)
PublicHoliday = "2. Weihnachtstag"
Case Else
PublicHoliday = ""
End Select
End Function
Column S calculates the duration in days, between start and end date. In column T you enter the percentage of completion of the subproject, which is also shown in the Gantt Chart.
Gantt Chart
The latter three columns are essential for the Gantt chart. The chart is done using Excel formatting only, without any additional VBA. Each subproject is shown here from its start date to its planned end date. The overall completion of each milestone is also shown. Areas colored in light blue are considered to be already completed, whereas dark blue reflects the percentage of the subproject that is still uncompleted. The vertical blue line indicates the current day.
You can toggle between daily and weekly grouping via radio buttons.
Day selection shows the Gantt chart on a daily time frame.
Otherwise if you choose week, then the timeline will be displayed as follows.
You can also scroll the timeline using the slider at the top.
Upper ribbon
The first button from the left is the green traffic light, which can filter different statuses.
For example, if you click on Phase 3 unhide, only these states will be displayed.
To display everything again, just select "all phases unhide". This is basically just to illustrate how much you can already achieve in interaction with spreadsheets and a little bit of VBA.
The Team icon helps one to view the tasks filtered by different people.
Just as an idea, you can of course combine as many subqueries as you like. Depending on what is useful for the evaluation an additional time filter might make sense to you.
The idea of the email button is to select one task and send this data to a recipient in a convenient way.
Please note that this solution is designed for Lotus Notes email clients.
Sub LotusMailSenden()
..
MailDoc.body = "Hello " & ActiveSheet.Cells(ActiveCell.Row, 7) & "," & "<br><br>the task " & ActiveSheet.Cells(ActiveCell.Row, 2) & " (with the ID number: " & ActiveSheet.Cells(ActiveCell.Row, 19) & ") starts at " & ActiveSheet.Cells(ActiveCell.Row, 10) & " and is due until " & ActiveSheet.Cells(ActiveCell.Row, 11) & "." & "<br>Priority is set to " & ActiveSheet.Cells(ActiveCell.Row, 9) & "." & "<br>Please ..." & "<br><br>Kind regards," & "<br>" & Application.UserName
..
Let me know if you need solutions for e.g. MS Office Outlook with VBA or Python.
The checkbox for showing and hiding columns was created with a simple VBA routine.
Private Sub CheckBox1_Click()
If ActiveSheet.CheckBox1 = True Then
Columns("H:H").EntireColumn.Hidden = False
Else
Columns("H:H").EntireColumn.Hidden = True
End If
End Sub
Since team comments can be quite important, we will take a closer look at the implementation. Clicking on the speech bubbles icon will open this form.
So you can enter a comment..
..which will automatically add user and time stamp:
Same counts for any additional comments, so you gain a history of all comments.
That is the VBA code for these comment functions:
Sub KommentarErfassen()
Dim Kom As Comment
Dim S As String
S = InputBox _
("Enter your comment", "Create comment")
If S = "" Then Exit Sub
Set Kom = ActiveCell.AddComment
Kom.Text Application.UserName & Chr(10) _
& Date & Chr(10) & S
With Kom.Shape.TextFrame
.Characters.font.Name = "Courier"
.Characters.font.Size = 12
.AutoSize = True
End With
End Sub
Sub KommentareErgänzen()
Dim sAlt As String
Dim sNeu As String
sNeu = InputBox _
("Add a comment", "Add comment")
If sNeu = "" Then Exit Sub
With Selection
On Error Resume Next
sAlt = .Comment.Text
If sAlt = "" Then .AddComment
sNeu = sAlt & Chr(10) & Application.UserName _
& Chr(10) & "Kommentar vom " & Date & _
Chr(10) & sNeu & Chr(10)
.Comment.Text sNeu
.Comment.Visible = True
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub
We see that you can already get a lot out of Excel with VBA. However, at some point the moment will come when you will reach the limits with it. Fortunately, there is Python, which leaves the limitations of MS VBA behind. In another article we have already learned how to get the most out of spreadsheets with Python. In this article, therefore, only a very brief example of a possible extension using Python is given. For example, let’s use Python to create a word cloud of project categories from column K.
from wordcloud import WordCloud
import matplotlib.pyplot as plt
text = 'Project Category A Project Category B Project Category A Project Category C Project Category A Project Category C Project Category B Project Category F Project Category A Project Category E Project Category A Project Category C Project Category D Project Category E Project Category E Project Category F Project Category A Project Category G'
wordcloud = WordCloud().generate(text)
plt.imshow(wordcloud, interpolation="bilinear")
plt.show()
We will use Python to add real database functionality while still using this Excel template as a frontend in another post yet to come. Until then you can download the Excel Project Management template from my Github repository.
Conclusion:
We have seen that you can already do remarkable things with simple means, if you only know a little bit of programming. In a next article we will extend this Excel template to a real database application thanks to Python.
Until then, many thanks for reading! I hope this article is helpful for you. Feel free to connect with me on LinkedIn, Twitter or Workrooms.