Making a Mission Updates Tracker in Excel Utilizing VBA | by Himalaya Bir Shrestha

Purpose

I aimed to create an utility for monitoring undertaking updates and logging them. I wished to have one sheet, the place I entered every new replace for a activity inside a undertaking. I additionally wished to trace the time I logged this info in my file. With the clicking of a button, I wished to maneuver every new replace together with the time it was logged to a separate sheet for logging the knowledge.

For this function, I created an Excel file with two completely different sheets. The primary sheet was referred to as ProjectTasksTracker, and the second sheet was referred to as Logbook. Each these sheets comprised of identical header row containing six columns: DateTime, Mission, Duties, Accountable Workers, Standing, and Updates.

I used the =NOW() operate in Excel within the DateTime column to get the true time. I allowed three choices within the dropdown menu within the Standing column: Began, In Progress, and Full. I additionally created a button named Replace Logbook to log all the knowledge routinely with out duplication within the Logbook sheet. The ProjectTasksTracker sheet regarded like proven beneath:

Construction of ProjectTasksTracker sheet. Illustration by Creator.

Coding Steps

I began with making a subroutine inside a module in VBA.

Step one concerned defining the workbook object wb for the file and two worksheet objects, ws1 and ws2 for ProjectTasksTracker sheet and Logbook sheet respectively. The code is given within the snippet beneath:

'Outline workbook and two worksheets.
Dim wb As Workbook
Dim ws1 As Worksheet 'Mission tracker worksheet
Dim ws2 As Worksheet 'Logbook worksheetSet wb = ThisWorkbook
Set ws1 = ThisWorkbook.Sheets("ProjectTasksTracker")
Set ws2 = ThisWorkbook.Sheets("Logbook")

2. The second step concerned writing code to depend the variety of rows and columns within the two sheets. This can be performed manually. Nevertheless, because the variety of rows can change whereas getting into undertaking updates, this course of is up to date. The variety of columns is stored fastened (6) to maintain the construction of the 2 sheets constant. Nevertheless, it is usually coded for demonstration functions.

Within the code snippet beneath, lr1 counts the variety of rows within the worksheet ws1 based mostly on column A. lc1 counts the variety of columns in the identical worksheet based mostly on row 1.

'Rely the variety of rows and columns in ProjectTasksTracker sheet
Dim lr1, lc1 As Integer
lr1 = ws1.Cells(Rows.Rely, “A”).Finish(xlUp).Row 
lc1 = ws1.Cells(1, Columns.Rely).Finish(xlToLeft).Column

Notice: It’s attainable to make use of the reference to a particular cell whereas working with macros. That is helpful whereas working with datasets with the opportunity of adjustments. For instance, I created a named vary Updates to seek advice from cell F1 within the ProjectTasksTracker sheet. In case, one column is added earlier than it, Updates will then seek advice from cell G1.

Updates named vary is referred to within the code with update_cell as proven beneath. The column quantity it belongs to is referred to with update_column and the column quantity when it comes to alphabet is given by update_col.

Dim update_cell As Vary
Set update_cell = ws1.Vary(“Updates”)Dim update_column As Integer
update_column = update_cell.Column
Dim update_col As String
update_col = Chr(update_column + 64)
MsgBox "Replace column belongs to: Column " & update_col

MsgBox to show location reference of Updates based mostly on the code above. Illustration by Creator.

Within the code within the subsequent step, we’re going to seek advice from the Updates column with column quantity 6 immediately for comfort.

3. The third step is an important on this course of. On this step, I looped via every row (besides the header row and Datetime column) within the ProjectTasksTracker sheet and carried out the next operations given as three sub-steps:

a. For every row within the ProjectTasksTracker sheet, I checked whether or not the Updates column is empty or not for every activity. If a specific row in ProjectTasksTracker had updates, then I counted the variety of rows within the Logbook sheet and assigned the depend as an integer referred to as lr2. Moreover, I declared a boolean datatype referred to as valuesMatch and assigned it as False by default.

b. Subsequent, I created a nested loop to loop via every row within the Logbook sheet and checked whether or not the content material of every column of the row within the ProjectTasksTracker sheet (outlined as vary rg1) matched with the content material of every column of any row within the Logbook sheet (outlined as vary rg2). If there aren’t any matches between rg1 and any worth of rg2, then it will imply that the replace in a specific row within the ProjectTasksTracker sheet had not been logged into the Logbook sheet earlier than. The valuesMatch would stay False. If the content material of a row within the ProjectTasksTracker sheet matched with any row within the Logbook sheet, then it meant that the row had already been logged earlier than. In that case, the worth of the valuesMatch could be modified to True.

c. If the valuesMatch was True on the finish of each for-loops, then there could be no additional processes. If the valuesMatch was False on the finish of two for-loops, then the row from the ProjectTasksTracker sheet (together with the Datetime column) could be copied and pasted to the Logbook sheet.

The steps 3a, b, and c have been coded within the gist beneath: