Macro to reset Validation box

[MichaelRobert]

My spreadsheet contains a range of validated cell (b5:b10) . Each cell uses a
different source table. But each table has a setting that is the 'starting'
position for each. The starting position is variously "Empty" or "Select
one". For example, one source is:

Empty
Amber
Blue
Clear
Green
Red

A user will select one of these options from each of the validation boxes,
and the spreadsheet opens next time at the selections made last time.

I want to create a Macro that resets whatever has been selected back to the
Empty position, ready for the next selection.

I have tried recording a macro where I select each cell, click the drop-down
arrow on the validation box and select the Empty option . But the only thing
that is recorded is the identity of the lsat cell that I selected.

Help please?

Thanks

Mike

 

[JLGWhiz]

You can use the Workbook_Open event to set it to wherever.

 

[MichaelRobert]

Thanks for the function to trigger the reset. But what I need is the macro to
perform the reset. Unless the Workbook_Open not only fires the macro, but
also re-opens the Workbook at the required condition.

Even then, I don't want to reset the entire Workbook - only a few cells on
Sheet 1, or - at a pinch - all of Sheet 1.

Or maybe I am missing something

Mike