Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF's

PharmaSUG 2017 - Paper AD15

Excel-VBA Tool to Auto-Create Validation Log and Review Form using

List of TLF’s

Balaji Ayyappan, inVentiv Health, North Carolina, USA

ABSTRACT

When a clinical study gets started we start with the List of Reports (Tables, Listings, Figures and

Appendices) to be created for interim analysis (like BDR’s, DMC, SRT deliveries) and Final CSR delivery.

Creating and maintaining the Validation Log and Review Form documents are vital and needed for the

submission process. We created a tool to auto create the Validation Log and Client Review Form.

Validation Log File is created with pre-defined columns for the corresponding TLF’s - where

programmers/statisticians need to enter their work status, comments, validation comments, initials and

dates during work progress. In Review Form, client fills their comments, issues/solutions discussed during

review meeting with initials and dates. This tool helps to create these documents in time efficient way and

avoid manual error. This tool is developed using Excel-VBA technology.

INTRODUCTION

The Objective of this tool is to create the Validation Log and Review Form which are used for BDR/CSR

delivery for the given List of Reports (LOR). LOR file is used as the source for creating this review

document. The whole tool is developed using Excel VBA macros. It has Add-in buttons and Excel VBA

USERFORMS for creating Validation Log or Review Form. Screenshots are displayed under each steps.

PROCESS

Step 1: When you open the macro enabled excel file, go to ADD-Ins Tab in the main menu, Click

“Create Validation Log” or “Create Review Log” from the list.

Step 2:

USERFORM is opened in front of the sheet, where you will be seeing different options to select

for creating required file.

Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF’s, continued

Step 3:

There are two ways of creating it.

i. Create using LOR Sheet.

ii. Create using Selected Column.

Step 4: To Create Validation Log/Review Form using LOR sheet as the source, in which all Tables,

Listings, Figures and Appendices information are filled with their respective Table No# and Table Title.

Select the “Use LOR Sheet” option; select the corresponding LOR Sheet name that needs to be used.

Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF’s, continued

Step 5:

After selecting required LOR sheet name, click on the “Folder Path” to choose the “Output

Directory” and enter the “File Name” that needs to be created. Then click “Create Validation Log /

Review File option” button to create the new file in the given name. All the reports in the LOR Sheet

carried to output file.

(In the above example: Validation_Log_A00 name is given, Validation_Log_A00.xls will be created in the

specified directory, current date format will be automatically added to given file name.)

Step 6: Suppose if you have LOR Sheet and only certain set of reports needed for BDR1, BDR2, DMC,

SRT, CSR delivery, then we need to select the corresponding Column that needed for Validation/Review

file. Please check the below screen shot example, where LOR Sheet has BDR1, BDR2, etc., columns.

Step 7: If you need to create (Example: BDR1 delivery) Validation Form, you need to select “Use LOR

Column” option, select required sheet name and “BDR1” column from the list. Fill the “Folder path” and

“Output File Name” information and click “Create Validation Log” button, it creates the output file for the

reports which are marked “X” in the selected column (similar to step 5, output name added with current

date, Output File is created in specified directory.)

Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF’s, continued

Step 8:

Suppose if you want to import particular sheet from another file (external file) that need to be

used for creating Validation Log, then below method is used. Click “Browse” button to select the required

file, click on “Use LOR Sheet Name” and select the required sheet name that need to be imported, then

click the “Import Excel Sheet” button, it import the selected sheet to current file which can be used to

create Validation Log.

Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF’s, continued

Step 9: “

Clear” Button is used to clears all the filled USERFORM information, “Cancel” Button close the

USERFORM in the screen.

VBA CODE

Sub Crt_BDR_Using_ShtNm()

'Method 1

Dim ROW_CNT, last_L, last_S, last_T, last_F, req_note, req_text,

lstrqdrow As Long

Dim Rqrd_Shtnm As String

Rqrd_Shtnm = UserForm1.ShtNm_ComboBox1.Text

ROW_CNT = Sheets(Rqrd_Shtnm).Range("A65536").End(xlUp).Row

For i = 1 To ROW_CNT

If Sheets(Rqrd_Shtnm).Cells(i, 1) = "L" Then

last_L = i

End If

Next i

lstrqdrow = WorksheetFunction.Max(last_L, last_S, last_T, last_F)

If req_note > 0 And lstrqdrow > 0 And req_note <> lstrqdrow + 1

Then

MsgBox "Blank records between LOR Note and Last Report in LOR"

End If

Sheets(Rqrd_Shtnm).Range("B" & req_text + 1 & ":C" & lstrqdrow).Copy

Destination:=Sheets(UserForm1.TxtBox_NwBDR_Shtnm.Text).Range("A10")

Sheets(Rqrd_Shtnm).Range("B" & req_text + 1 & ":C" & lstrqdrow).Copy

Sheets(UserForm1.TxtBox_NwBDR_Shtnm.Text).Activate

Range("A10").Select

ActiveSheet.Paste

Application.CutCopyMode = False

OUTPUT

Below Validation Form or Review Log is created automatically in specified Directory.

Validation Log:

Excel-VBA Tool to Auto-Create Validation Log and Review Form using List of TLF’s, continued

Review Log:

CONCLUSION

First two columns (Table No#, Table Tittle) are automatically filled using this tool, other column headers

are predefined. This tool helps to create these documents in time efficient way and avoid manual error.

ACKNOWLEDGEMENTS

I would like to thank Fernando Enriquez for providing ideas and helping to implement this utility.

CONTACT INFORMATION

Your comments and questions are valued and encouraged. You can contact us at:

Balaji Ayyappan,

inVentiv Health,

1001 Winstead Drive,

Cary, NC 27513

Phone: (919) 337 1427

Email: balaji.ayyappan@inventivhealth.com

SAS® and all other SAS® Institute Inc. product or service names are registered trademarks or

trademarks of SAS® Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are trademarks of their respective companies.