Display a Object from Non Value List

This is hard to explain and not sure if there is a possible work around for this. In my example you will see my name list that changes
with a array to data validation. Seems like you can find ways around things most of the time but this time I need some expert help.

zplugger,

Attached is a modified version of your example workbook.
I created a macro in a standard module named "DisplayFace"

Please Login or Register  to view this content.

I then used the workbook_open event and sheet 'TablePlan' worksheet_change event to call that macro.
In the ThisWorkbook code module:

Please Login or Register  to view this content.

In the Sheet1 (TablePlan) code module:

Please Login or Register  to view this content.

It should also be noted that I changed how the 'Lists' sheet works. First I got rid of the named range "NameCheck". I made row 1 a header row with actual data starting in row 2. In column A is the full list of names. I created a dynamic named range to reference that full list named "list_Names" and defined it with this formula:

Please Login or Register  to view this content.

Then in column B I used a formula to get the list of unused names. In cell B2 and copied down to B25 is this formula:

Please Login or Register  to view this content.

Next I created a dynamic named range to reference that list of unused names named "list_UnusedNames" and defined it with this formula:

Please Login or Register  to view this content.

You'll see that the sub DisplayFace uses the dynamic named range "list_UnusedNames" to determine when there are no longer any names left unused, which is how the smiley face and frowny face swap visibility. To test it out, just use up all the names (I think I saved it with only 1 name left to use) and you'll see the faces switch. To switch back, delete any name in the guest list (column D on sheet 'TablePlan').

You're very welcome

in the Tables sheet module:

Please Login or Register  to view this content.

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste