Vba macro excel project with userform for conditional entry in multiple sheets.
Budget: €20 – €80 EUR
For convenience I divide the illustration of the project into 2 different parts.
PART1:
I have an excel file with an "CEL ANNUALE" sheet which shows some personal data and some characteristics of the users of a service. The data is entered into the sheet through a userform called "userform1". The data entered through userform 1 are always recorded in the main "CEL ANNUALE" sheet
Among these data are:
- an "insertion date" field which concerns the date on which a user was added to the service.
- a "resignation date" field which reports the date on which a user concluded the service.
In addition to the "CEL ANNUALE" sheet there are 12 other sheets called "CEL 01-24" (which refers to the service status as of January 2024), "CEL 02-24" (which refers to the service status as of February 2024) and so on with the sheets "CEL 03-24", "CEL 04-24", "CEL 05-24", up to "CEL 12-24".
The data entered in userform1 must be automatically inserted into these monthly sheets only if the following conditions occur simultaneously:
- the "insertion date" field reports a date equal to or earlier than the reference month of the sheet.
- the "resignation date" heading is left blank or shows a date equal to or later than the reference month of the sheet.
For example: if the userform1 form is filled in with the data of a user who started the service on 10 February 2024 (entry date = 10/02/2024) and ended it on 20 November 2024 (resignation date = 20/11/ 2024) once the userform1 data has been sent, these data must be entered automatically in the "CEL ANNUALE" sheet and in all the "CEL XX-2024" sheets (from "CEL 02-2024" to "CEL 11-2024") except in the sheet "CEL 01-2024" and in sheet CEL 12-2024 since on 31 January 2024 the user had not yet been registered for the service and on 1 December 2024 the user had already concluded the service.
For part 1 I provide the excel file with working userform for entry into the "CEL ANNUALE" sheet.
It is important to note that the sheets will be distributed in protected mode so that those who fill them out can only enter data via userform1. For this reason, in the code of userform1 it is foreseen that when it is opened (which occurs via a button) the sheets are unprotected and when the userform is sent the sheets are protected again.
Part 1 still needs the following developments:
1a) restriction on the entry of duplicate data: if a user is already present in the CEL ANNUALE sheet the form returns a warning informing that the user is already present in the list and prevents new entry
1b) conditional automatic insertion of the data entered in the userform1 depending on the dates entered in the "entry date" field and in the "resignation date" field, as already explained.
1c) possibility of creating an "edit record" button which opens a "userform2" similar to "userform1", allows you to search for the record to be modified and represent its data in the userform so that it can be updated, applies the changes to the existing records in all sheets (CEL ANNUALE, CEL 01-24, CEL 02-24 etc.) reapplying the conditions already illustrated if the "insertion date" and "resignation date" fields are modified.
1d) alphabetical sorting of data (by "surname" column) when a new record is inserted into a sheet.
PART2:
The second part concerns the possibility of transferring some data present in the "CEL ANNUALE" sheet to a new sheet called "BORSE-TIROCINI".
In this case there must be a button in the sheet that allows you to open a new userform, the "userform3" where, thanks to a search box, some fields of a record of your choice present in the "CEL ANNUALE" sheet can be retrieved and entered as such. data in the " BORSE-TIROCINI " sheet by inserting them in the first free row of the table and sorting the rows in alphabetical order according to the "surname" column.
Clarifications:
1) For the realization of this project I will provide the excel file with the userform1 ready, except for the functions described above (part 1 and part 2)
2) in each sheet there is data formatted as tables. All columns managed via userform1 (all sheets with the "CEL" prefix) have the same position in all sheets even if to the right of these columns there are columns that can be filled in directly in the Excel worksheet.
3) the excel file (columns, cells and an so on) is in italian language so a little effort will be needed to interpret the column headings
4) as soon as possible I will make the excel file available so you can study it (in the next few I will finish some small adjustments)
PART1:
I have an excel file with an "CEL ANNUALE" sheet which shows some personal data and some characteristics of the users of a service. The data is entered into the sheet through a userform called "userform1". The data entered through userform 1 are always recorded in the main "CEL ANNUALE" sheet
Among these data are:
- an "insertion date" field which concerns the date on which a user was added to the service.
- a "resignation date" field which reports the date on which a user concluded the service.
In addition to the "CEL ANNUALE" sheet there are 12 other sheets called "CEL 01-24" (which refers to the service status as of January 2024), "CEL 02-24" (which refers to the service status as of February 2024) and so on with the sheets "CEL 03-24", "CEL 04-24", "CEL 05-24", up to "CEL 12-24".
The data entered in userform1 must be automatically inserted into these monthly sheets only if the following conditions occur simultaneously:
- the "insertion date" field reports a date equal to or earlier than the reference month of the sheet.
- the "resignation date" heading is left blank or shows a date equal to or later than the reference month of the sheet.
For example: if the userform1 form is filled in with the data of a user who started the service on 10 February 2024 (entry date = 10/02/2024) and ended it on 20 November 2024 (resignation date = 20/11/ 2024) once the userform1 data has been sent, these data must be entered automatically in the "CEL ANNUALE" sheet and in all the "CEL XX-2024" sheets (from "CEL 02-2024" to "CEL 11-2024") except in the sheet "CEL 01-2024" and in sheet CEL 12-2024 since on 31 January 2024 the user had not yet been registered for the service and on 1 December 2024 the user had already concluded the service.
For part 1 I provide the excel file with working userform for entry into the "CEL ANNUALE" sheet.
It is important to note that the sheets will be distributed in protected mode so that those who fill them out can only enter data via userform1. For this reason, in the code of userform1 it is foreseen that when it is opened (which occurs via a button) the sheets are unprotected and when the userform is sent the sheets are protected again.
Part 1 still needs the following developments:
1a) restriction on the entry of duplicate data: if a user is already present in the CEL ANNUALE sheet the form returns a warning informing that the user is already present in the list and prevents new entry
1b) conditional automatic insertion of the data entered in the userform1 depending on the dates entered in the "entry date" field and in the "resignation date" field, as already explained.
1c) possibility of creating an "edit record" button which opens a "userform2" similar to "userform1", allows you to search for the record to be modified and represent its data in the userform so that it can be updated, applies the changes to the existing records in all sheets (CEL ANNUALE, CEL 01-24, CEL 02-24 etc.) reapplying the conditions already illustrated if the "insertion date" and "resignation date" fields are modified.
1d) alphabetical sorting of data (by "surname" column) when a new record is inserted into a sheet.
PART2:
The second part concerns the possibility of transferring some data present in the "CEL ANNUALE" sheet to a new sheet called "BORSE-TIROCINI".
In this case there must be a button in the sheet that allows you to open a new userform, the "userform3" where, thanks to a search box, some fields of a record of your choice present in the "CEL ANNUALE" sheet can be retrieved and entered as such. data in the " BORSE-TIROCINI " sheet by inserting them in the first free row of the table and sorting the rows in alphabetical order according to the "surname" column.
Clarifications:
1) For the realization of this project I will provide the excel file with the userform1 ready, except for the functions described above (part 1 and part 2)
2) in each sheet there is data formatted as tables. All columns managed via userform1 (all sheets with the "CEL" prefix) have the same position in all sheets even if to the right of these columns there are columns that can be filled in directly in the Excel worksheet.
3) the excel file (columns, cells and an so on) is in italian language so a little effort will be needed to interpret the column headings
4) as soon as possible I will make the excel file available so you can study it (in the next few I will finish some small adjustments)