Introduction to Excel & VBA - Part 2

Here is the promised continuation of "Introduction to Excel & VBA - Part 1", starting with step three:

Step 3: Create & update project overviews (VBA)

In the last step, we want to ensure that the project overviews can be created automatically with a single click. To do this, we use the developer tools (CommandButton) and VBA.

Note: To open the VBA editor in Excel, simply press the key combination [Alt] + [F11]. Alternatively, the editor can also be accessed via the "Developer tools" menu (may first need to be activated via "File " -> "Options " -> "Customize ribbon" ).

• Developer tools" tab -> "Insert" -> ActiveX controls -> "Insertcommand button" in the project list sheet, for example
• Activate "Design mode" -> right-click on button

• In addition to size, font style, colors, etc., the naming in the editor can also be adjusted via "Properties" (without spaces!).
• The name displayed on the button can be adjusted via "Command button object" -> "Edit".
• Via "Show code", the editor directly opens the appropriate sub into which we can insert our code.

When you click on the button, the individual projects are now created and filled in accordingly:

In order to obtain the current version the next time the project overviews are called up, any new projects and/or new employees must be included in the projects. This can be easily solved by first deleting all overviews and then rebuilding them. We can use a simple procedure for this.

First, we add a new module in the editor and store the code for the sub "DeleteProjects()" there.

Option Explicit
Sub ProjekteLöschen()
'Variable definieren
Dim i As Long
'Anzeigeänderungen abschalten (Optik)
Application.DisplayAlerts = False
'Alle Blätter bis auf Projektliste, Mitarbeiterliste, Template und eventuelle ausgeblendete Blätter werden gelöscht
For i = ActiveWorkbook.Worksheets.Count To 1 Step -1
If Sheets(i).Name <> "Projektliste" And Sheets(i).Name <> "Mitarbeiterliste" And Sheets(i).Name <> "Template" And Sheets(i).Visible = xlSheetVisible Then Sheets(i).Delete
Next i
'Anzeigeänderungen einschalten
Application.DisplayAlerts = True
End Sub

We then add the call "Delete projects" to our original code. This means that when the "Create projects" sub is started, the "Delete projects" sub is called first (i.e. started).

Option Explicit
Private Sub ProjectsCreate_Click()
'Simple procedure --> Sub ProjekteLöschen is started after clicking on CommandButton
Call ProjectsDelete
'Define variables
Dim Cell As Range
Dim WS As Worksheet
Dim Range As Range
Set WS = ThisWorkbook.Sheets("Project list")
Set Range = Sheets("Project list").Range("Projects")
'Switch off display changes (optics)
Application.ScreenUpdating = False
'For each project stored in the project list, the Template sheet is copied once and renamed according to the project name
    For Each Cell In Range
    Sheets("Template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = Cell
    WS.Activate
    Next Cell
'Switch on display changes
Application.ScreenUpdating = True
'Show notification after creating the overviews
MsgBox "All project overviews have been created."
End Sub

If, for example, project E with employee Max Mustermann is added, clicking on the command button first deletes the existing project overviews and then recreates them, giving us the current version of the overviews.

This is a simple application example that can be transferred to other concepts. We use this approach, for example, when preparing the quality assurance measures in one of our projects.