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

Have you considered using data validation instead?
Contextures.com (http://www.contextures.com/xlDataVal01.html <<< this is
the 1st chapter of several chapters, maybe chapter 7?) has excellent
information on various type of data validations that do not require any vba
coding.

--
Thx
MSweetG222



"KeLee" wrote:

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

Oops - posted to wrong post... sorry KeLee, this was intended for someone else.
--
Thx
MSweetG222



"MSweetG222" wrote:

Have you considered using data validation instead?
Contextures.com (http://www.contextures.com/xlDataVal01.html <<< this is
the 1st chapter of several chapters, maybe chapter 7?) has excellent
information on various type of data validations that do not require any vba
coding.

--
Thx
MSweetG222



"KeLee" wrote:

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