Good Morning All
I'm having a bit of an issue - i'm updating a form we use for customers (schools) buying in to annual services.
Last year we used this form, but left school details fields as freetext which was good, however the numbers in the pupil headcount were either lower or higher than the schools census data (the data they submit to the local authority to confirm number of pupils at the school) Our charging is based on this number and meant last year we either undercharged or overcharged schools.
My issue is - I need to allow new schools/schools not on the list to be included. I've allowed freetext in data validation for Establishment Name so this field can be overtyped, this means that the other details (Dfe, type, Pupil headcount) can too be overtyped however my problem is that when the other details fields are overtyped they lose the formula. This is fine however if a mistake is made and the school IS actually on the list, the formulas are lost and the cells remain empty if a user then selects from the dropdown list.
I've been looking for perhaps some VBA to add so that if these cells are empty, VBA would re-add the formula back into the cell. Is this possible or is there a better way?
Also another issue I'm experiencing is protecting the fields - I'm trying to also find some VBA so that if these cells are autopopulated (because a school is selected from the dropdown list) then leave them protected (to prevent overtyping) however if the school is not on the list, so the user manually types in their school, these fields need to be unprotected so these details can be entered.
I'm having a bit of an issue - i'm updating a form we use for customers (schools) buying in to annual services.
Last year we used this form, but left school details fields as freetext which was good, however the numbers in the pupil headcount were either lower or higher than the schools census data (the data they submit to the local authority to confirm number of pupils at the school) Our charging is based on this number and meant last year we either undercharged or overcharged schools.
My issue is - I need to allow new schools/schools not on the list to be included. I've allowed freetext in data validation for Establishment Name so this field can be overtyped, this means that the other details (Dfe, type, Pupil headcount) can too be overtyped however my problem is that when the other details fields are overtyped they lose the formula. This is fine however if a mistake is made and the school IS actually on the list, the formulas are lost and the cells remain empty if a user then selects from the dropdown list.
I've been looking for perhaps some VBA to add so that if these cells are empty, VBA would re-add the formula back into the cell. Is this possible or is there a better way?
Also another issue I'm experiencing is protecting the fields - I'm trying to also find some VBA so that if these cells are autopopulated (because a school is selected from the dropdown list) then leave them protected (to prevent overtyping) however if the school is not on the list, so the user manually types in their school, these fields need to be unprotected so these details can be entered.
