Create a protected and validated Family Medicine Excel template

[ChasNelson1990]

Attached is an Excel file. This file has multiple sheets.

The sheets of interest are the sheets who's names begin with “FD”. E.g “FD BETCU” The sheet name indicates “Family Doctor who's name is Betcu”.

You can ignore all other sheets.

The sheets of interest show the excel template that family doctors are expected to fill in on a weekly basis.

From this we want to create a robust excel template that can be easily read and edited.

• Get rid of the metadata table at the top - this will be added in CKAN.
• Users should only be able to edit the numbers inside the big table. Protect all other cells (including the Total column which should be an automated sum of the other columns). I’m assuming a password will be sued to protect the cells. Record the password here in this Jira ticket.
• There should be some validation to only allow numbers to be added to this table (disallow text dates etc…)
• It should maintain a similar user-friendly and easily readable format - keep the colouring/formatting/layout as close as possible to the existing table.
• Upload the final excel template to this Jira ticket.

[ChasNelson1990]

Password for the workbook and sheet will be : mZWU88Jso2

Password tor the macro will be : 1466ae55 Originally posted by A-Souhei on Jira.

[ChasNelson1990]

The template is attached on the ticker. Originally posted by A-Souhei on Jira.

[ChasNelson1990]

Just verifying if all cells got the format and done. Originally posted by A-Souhei on Jira.

[ChasNelson1990]

Code for the VBA script :

{noformat}Private Sub Worksheet_Change(ByVal Target As Range)

Dim CheckRange As Range
Set CheckRange = ThisWorkbook.Sheets("FD").Range("D3:W36")
Dim Cell As Range
IsPositiveInt = False

If Target.CountLarge <> 1 Then

    Application.EnableEvents = False

    For Each Cell In Target
        If Not Intersect(Cell, CheckRange) Is Nothing Then

            IsPositiveInt = False
            If IsNumeric(Cell.Value) Then
                If Int(Cell.Value) = Cell.Value And Cell.Value >= 0 Then
                    IsPositiveInt = True
                End If
            End If

            If Not IsPositiveInt Then
                MsgBox "Some values are not a positive integer or 0. Please enter valid values.", vbExclamation, "Error"
                Application.Undo
            End If
        End If
    Next Cell

    Application.EnableEvents = True

Else

    If Not Intersect(Target, CheckRange) Is Nothing Then

        Application.EnableEvents = False

        IsPositiveInt = False
        If IsNumeric(Target.Value) Then
            If Int(Target.Value) = Target.Value And Target.Value >= 0 Then
                IsPositiveInt = True
            End If
        End If

        If Not IsPositiveInt Then
            MsgBox "The value in " & Target.Address & " is not a positive integer or 0. Please enter a valid value.", vbExclamation, "Error"
            Target.Value = ""
        End If

        Application.EnableEvents = True

    End If

End If

End Sub{noformat} Originally posted by A-Souhei on Jira.

[ChasNelson1990]

Note:

• the .xlsx file does not accept macros so only regular validation works there, ie copy-paste breaks the validation.
• the .xlsm file has a macro whose code is below, prevents copy-paste issue but if user does not wish to enable macros it won’t work (macros can be unsafe so by default disabled) so it will just behave like the .xlsx file. Originally posted by A-Souhei on Jira.