K
KeLee
Hello MVP's
First, I am not a proficient VBA programmer by any stretch of the imagination!
I have set up Data Validation using dynamic contingent ranges.
A2's Validation = the named Dynamic Range "Departments" - an ever growing
list defined by the companies departments as dictated by a paste dump from
CRM software.
A3's Validation = "DeptEmployees" another dynamic range picking from the
list of all employees, only those that are in the department selected in A2.
User must pick a valid, standardised, department, and assign a current
employee to that role.
The lists of departments and Employees are ever changing, and updated lists
are pasted in automatically and correctly each week.
I then have 27 worksheets that users enter data into, each referring to
separate event rotas. Typically ~20 employees to an event.
My problem is that if an employee leaves the company, the outdated data can
be left in one of the 27 sheets.
I know this can be a problem, as when the new employee list is dumped in,
if the old employee is no longer on it, it conflicts with the Data Validation
Criteria if i actively check the data validation on that sheet.
I want a User to be able to push a button, or something, that allows them to
go to the next conflicting data, wherever it is in the Workbook, and correct
it, displaying a message if there are none left
I can do this for each individual sheet using the "Circle Invalid Data"
function
Sub CheckValidationCriteria()
Calculate
ActiveSheet.CircleInvalid
End Sub
Which shows the red circles, but I want the user to be taken to that cell,
and then subsequent invalid cells, over all the worksheets, as people
understandably find checking all 27 sheets one after the other laborious,
especially as there may only be one conflict in the entire workbook each week.
Any help is greatly appreciated as always
KeLee
First, I am not a proficient VBA programmer by any stretch of the imagination!
I have set up Data Validation using dynamic contingent ranges.
A2's Validation = the named Dynamic Range "Departments" - an ever growing
list defined by the companies departments as dictated by a paste dump from
CRM software.
A3's Validation = "DeptEmployees" another dynamic range picking from the
list of all employees, only those that are in the department selected in A2.
User must pick a valid, standardised, department, and assign a current
employee to that role.
The lists of departments and Employees are ever changing, and updated lists
are pasted in automatically and correctly each week.
I then have 27 worksheets that users enter data into, each referring to
separate event rotas. Typically ~20 employees to an event.
My problem is that if an employee leaves the company, the outdated data can
be left in one of the 27 sheets.
I know this can be a problem, as when the new employee list is dumped in,
if the old employee is no longer on it, it conflicts with the Data Validation
Criteria if i actively check the data validation on that sheet.
I want a User to be able to push a button, or something, that allows them to
go to the next conflicting data, wherever it is in the Workbook, and correct
it, displaying a message if there are none left
I can do this for each individual sheet using the "Circle Invalid Data"
function
Sub CheckValidationCriteria()
Calculate
ActiveSheet.CircleInvalid
End Sub
Which shows the red circles, but I want the user to be taken to that cell,
and then subsequent invalid cells, over all the worksheets, as people
understandably find checking all 27 sheets one after the other laborious,
especially as there may only be one conflict in the entire workbook each week.
Any help is greatly appreciated as always
KeLee