Data validation lists - delete cell and show value, not a blank

[JStirfry]

Hello!

How can I set a data validation list such that if the drop down value in the cell is deleted, the cell will automatically show a default value (i.e. "-Select-")?

For example, I have a worksheet with multiple drop down lists that I want to show a default value of "-Select-" once the sheet is opened. A user will select values from the lists that will impact the result of calculations throughout the entire spreadsheet. A user may accidentally "delete" the cell contents of the lists. If this happens, rather than the cell becoming blank, I want the cell to show a default value of "-Select-". This scenario occurs in multiple random locations throughout the spreadsheet, not just in one localized place.

I was able to find the following VBA code so far, but it only applies the concept over a range, rather than just individual cells that contain drop down lists. The problem with what I have coded so far is that every single blank cell in the range ends up with "-Select-" in it. Some of the lists are in ranges, but some are also scattered throughout the sheet. The problem I'm having with my current code is that every single blank cell in the range ends up with "-Select-" in it. I'm trying to get this to apply over the entire worksheet to ONLY cells that are drop down lists.

Is what I'm trying to accomplish even possible?

Example file can be found here:
https://drive.google.com/file/d/1VoO8VgFs3IJ0ALwqfk0i8gt69UE4vEKW/view?usp=sharing

Example code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
' If Target.Cells.Count > 1 Then Exit Sub 'turning this off allows multiple cells to be selected and deleted at the same time
If Not Intersect(Target, Range("f217")) Is Nothing Then
For Each cel In Range("f217")
Application.EnableEvents = False
If IsEmpty(cel.Value) Then cel.Value = "-Select-"
Next cel
End If
Application.EnableEvents = True
End Sub​

Thanks in advance!

 

[JStirfry]

Great, yes the macro works exactly as I imagined. However, I envision that this macro will not

Responses to your additional questions in red below:

2. What do you mean by "random SELECTED area of cells"? Can you give an example?
Maybe I randomly want to select B2:L33 (to clear a single table quickly...
or maybe I want to select only C25:L25...
or maybe I want to F7:L27...

As you can see, very random selections, but the idea is that if I press the "delete" button while in the macro, it only clears the cells containing data validation drop down lists, and everything else will remain intact.
Do you get the idea...does it help?

3. Is the table to be copied always the same table? Is it always the same size and is it always in the same location?
Yes, Yes, and Yes. Just as shown in the example, the table has been copied below the existing table, with two blank rows in between. However, will this cause issues with the last macro you wrote since it's written so specific to the rows (9, 12, 16 16)?

4. "then make Rows 10, 16 & 19 show "N/A" instead of "Select" pull-down" …. did you mean row 12 instead of row 10?
No, I did mean rows 10, 16 & 19.


I'm adding another one:
If C16 & C19 are "None", then C33 should be "None". This should apply across columns C through L.


By no means am I asking you to develop this entire code for me, but if you are willing to help, I'm taking each response you give to me as a learning opportunity! It's very generous of you to take the time to help me out!

 

[mumps]

Click here to download your file. Hopefully, I have everything covered. There are two buttons on the Work in Progress sheet. Click "Insert Table" and a new table will be inserted with the Room Number increased by 1. Before you click the "Clear Cells" button, select a range with the drop downs you want to clear.

 

[JStirfry]

My mind is absolutely blown right now!! I do believe you captured everything I was trying to accomplish! This is amazing, thank you so very much!

 

[mumps]

You are very welcome.

 

[JStirfry]

Let's say I wanted to create a new worksheet for each room instead of copying the table within the same worksheet...also while maintaining the functionality you've already developed for me? Is this possible?

Also, what about "undo". It doesn't appear that anything modified by VBA script can be undone...is there a work-around for that by any chance?

 

[mumps]

Click here to download your file.

Regarding your "undo" question, "undo" cannot reverse any action performed by a macro.