Error when Validation "Input message" modified by "Selectionchange" event in VBA

[julie_nickb]

I have have succeeded in modifying the Validation Input message for my input cells, by using the "Selectionchange" event in VBA.

All worked fine, until I did the following:

- used the "Format Cells" "Protection" tab to uncheck the "Locked" box for the input cells
- Protected the sheet with the "Review" tab

Now, when I click on my "Unlocked" cell, I get the following message:

Run time error 1004
Application defined or Object defined error

This is the code line where the error occurs.
.InputMessage = "YES"

If I unprotect the sheet, it works.
If I take out the modification of the Inputmessage, it does not crash.
Help please!

 

[super0mal]

Thanks Julie for your reply.
In my case, each column in the table has different a validation. There was quite a bit of fiddling to find a solution - but the key was NOT to use the input message at all, but to get the valid options from the formula(1) and use it in either a cell comment, or a userform listbox.

I found two ways to address it.
1) I inserted a cell comment.
I did that using VBA also, but also included the validation formula(1) as part of the message, so that users would know what are the valid choices when they hover over the cell.

2) I also set up a userform which was on the double click event for cells in my table.
I set up a listbox and added the valid entries to that (using the validation formula(1) as above.
Unfortunately I cannot copy my code here, because its on a secure account, but here's the guts of it:

the vba sub was activated using either the on change event (for the comment) or on initialisation of the userform (set up on the dblclick event).

I used the "LIKE" function to match the cell selection (if 1 char long) or a select case for multiple chars. Yes I had to set up a search for each column in the main table.

eg. If Sel Like "[S,E]" then
call the function

eg2 select case Sel
case is = "BA", "CA", "CW"
call the function

I created two functions
1) to lookup the guts of the table of valid values (TblGuts)
For this I used a for x loop 1 to UBound(myRange) (which range(tblName)) - the table where the valid selections were.

I set up a function with parameters (value of the cell, valid values, the field name, and the table name) Note that I have a table for each column. The values were populated dependant on which cell was selected.

The function itself called another function to look up the validation table for the valid values.

 

[super0mal]

...and then....
the second function
added the validation error message (funny that that worked and the input msg didn't).
added the cell comment with data from the first function and function params.

I know this sounds complicated, and it was, but there was a way around it, and like you, I couldn't find the actual fix to the validation Input message not accepting variables.

Good luck
BIGm