Data Validation on SelectionChange - Works but slow!

Hi guys,

Firstly, this forum is exceptional - 9/10 out of 10 I manage to find an answer. This time round though I am a little stuck.

On one of the spreadsheets I have a type of role mapping exerise - if it's a 1:1 then only a '1' can be entered, if it's a 1:M an 'x' can be entered. Only these values can be entered (Due to more VBA that runs a report for every name and then it lists the roles etc they are mapped to and different counts going on).

Due to the roles listed a long the top being dynamic and changing when imported (roles being added and removed) I need the cells data validation to change with that range.

Please Login or Register  to view this content.

It is a little clunky and I have tried many different ways, else statements etc but nothing speeds it up.

The problem I have is that every time I click a cell the spinning timmer pops up for a split second, but enough to be a problem. Is anybody please able to help tighten up this code to be far sexier and faster?

Thanks in advance...

If I understand what you are doing correctly, then maybe you avoid vba altogether? put something like:
=IF(A1="M",A2,A3)
as data validation list in A4, then put "1" in A2 and "x" in A3.

You should be extend this method to work for your columns.

Thanks for the quick reply.

So row 6 contains the detail whether it is 1:1 or 1:M. Each column needs to be validated for as many rows down as long as there are users (thus entirecolumn), based on the value in the column, in row6. Does that make sense?

Thanks,
J