Multiple Data Validation Checks

[stubbsj]

Hi Everyone -

I have a situation where I want to check multiple data validations before I allow a user to enter data in a cell.

I have a row where a user enters in a forecast for an amount that they want to spend. Within that row, I have a column for "Finance Approval". The approval cell uses a simple pull-down data validation list with "Approved", "Rejected". I also have another cell where I store the value of the user's "rights" based upon their login ID. So, an approved "Finance Approver" has his rights set to "Finance" when he logs in.

What I want to do is have a combined validation where only someone with the "Finance" rights can make changes to the "Finance Approval" column. If a use attempts to make a change to the restricted cell that does not have the proper rights, then use a Msgbox to inform the user that the dont have rights. Otherwise, they will see the pull-down list.

I'm posting this thread in the Programming Group because I'm not sure that it can be done simply using Data Validation.

What I would rather use a VBA solution that checks to see if a user has required rights to make a change to the cell, something like a "Before the Value of the Cell is Changed" approach. Then, if they have changed the cell value without the required rights, I can pop up a box "You do not have rights to modify this Cell Contents" and leave the cell value at it's previous value.

I hope all this makes sense and somebody can help me with this.

Thanks in advance!

Jim

[Steffen Thomsen]

Hi,

You could restrict it like this

Please Login or Register  to view this content.

[stubbsj]

Thanks Steffen.
I thought of doing that and it will work but isn't it "after the fact"? The user has already made a change. How do I put the changed value back to it's original (previous) value after the pop up message?

Is there a way to check before the value is changed? If not, I can use your solution but still nedd to know how to restore the previous value.

Please Login or Register  to view this content.

Jim

[Steffen Thomsen]

No this happen when a selection of a cell is made

Private sub Worksheet_SelectionChange(byval target as range)

The event your thinkin about is this one, this fires when a cell value changes.

Private sub Worksheet_Change(byval target as range)

[stubbsj]

Right you are Steffen. Thanks for the clarification. My brain is running on Empty today.

I'm going to mark this as solved.

Thanks again,
Jim