Hours Registration VBA APP
Excel VBA APP to register hours on a weekly basis with security and password protection. The App enables saving the data to a separate database file, adding new names, saving pdfs, and changing dates.
Functionalities of the app:
1 - Password protected so that people cannot manipulate prior filled in hours and only the current date
2 - Functionality to add more people to the App
3 - Time limit so that App will close after a certain amount of time
4 - Saving of a weekly hours PDF file
5 - Saving of hours to seperate database
App Components:
1 urenReg.xlsb : this is the landing page where people can access the controls of the app (such as add users and register hours). Furthermore, main controls are also located here.
2 masterData.xlsb : seperate database file where on a day-by-day basis hours are stored
Script urenReg.xlsb:
Workbook modules
Workbook_Open() : lock specific columns with a password protection when opening the workbook, and only allow editing the current day of the week.
Workbook_BeforeClose(): lock all the columns
Workbook_SheetChange(): call upon time action that closes the workbook after 10 minutes
Modules
dataTrans(): General Function that requires a password and performs file transfer to database, saves a pdf of week, and cleans the current week.
clearfillinData(): Function to clear data.
speed(): Function to speed up code execution by disabeling certain visual features.
slow(): Function to go back to default settings.
TimeSetting(): Function to close and save workbook after specified amount of time.
SelectSheetsToPrint(): Function saves the current current sheet in pdf format with the corresponding week as name.
savewb(): Function used to perform a save of workbook and saving the inputted name to an audit trail.
createOutputSheet(): Function creates a sheet with the data prepped in a table for transfer to the external database.
deleteDataInput(): Function that deletes the transfer sheet.
add_name(): Function that let users add new names to the hour registration form.
Button6_Click(): Function to go forward 1 week with the dates.
Button7_Click(): Function to go back 1 week with the dates.
Script masterData.xlsb:
Workbook modules
Workbook_Open() : Function to hide toolbar.
Modules
getDataUrenregColumns(): Function to get the data created in the transfer sheet and perform lookup and paste value based on name
lookupInnervalue(): Function to perform formatting on the data using to show hourly data
deleteDataInput():
speed(): Function to speed up code execution by disabeling certain visual features.
slow(): Function to go back to default settings.
