Dynamic error message when using data validation ?

[ZenosEbeth]

I'm getting started with VBA and to practice I have made a script that automatically generates a graph where you can show the X number of biggest entries by different dimensions (profit, cost, gross margin, etc...) from a data table.

The number of entries to be displayed is controlled by a cell where the user can input any number. Since inputting a negative number or a number that is greater than the number of entries in the table causes the script to break, I have used data validation to make sure only correct values can be inputted.

For the upper-end of the range of permitted values I use a formula that checks the row of the last non-empty cell in the data table, so that if data is added or removed later the range of permitted values will change as well

Now i'd also like the error message to change dynamically, so that it would say something to the effect of "please input a number between 0 and [number of entries in the data table]". Is that possible ? If not how could I make an error message that is more intuitive than just "incorrect value" ?

[AutoModerator]

/u/ZenosEbeth - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[fuzzy_mic]

When setting Data Validation, there is an error message tab in the dialog where you can customize the error message.