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

julie_nickb

New Member
Joined
Sep 13, 2018
Messages
46
Office Version
  1. 365
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

New Member
Joined
Jun 27, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

super0mal

New Member
Joined
Jun 27, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
...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
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top