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!

 

[mumps]

In the worksheet code module try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, vRng As Range
    Set vRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
    For Each rng In vRng
        If rng.Validation.Type = 3 Then
            If rng.Value = "" Then rng.Value = "-Select-"
        End If
    Next
End Sub

 

[JStirfry]

AMAZING! Exactly what I was looking for! Thank you so much!

 

[mumps]

You are very welcome.

 

[JStirfry]

mumps, wondering if you could assist again?

See example file here:
https://drive.google.com/file/d/1OpnH0eH7aWdvMFJy0rgCI9Eehf0iktnB/view?usp=sharing


What I'm trying to accomplish now is that when ANY main data validation cell changes (G2:J2 in this example), the data validation cells using the main cells indirectly (G8:J8 in this example), will automatically delete.

Once they are deleted, then the code you helped me with yesterday takes over and the default "-Select-" option would be shown.

Also, is there any way to do a global conditional formatting on the entire worksheet (as I already have set up for G8:J8) such that any cell containing "Select" will be highlighted?

I'm sure this is simple, but for me just starting out with it, it's not.

Thank you!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    'clear dependent drop down list cell after primary list changes
    'enter code here'
    'adds a default value to cell when data validation contents are deleted
    'Application.EnableEvents = False 'JDS added
    Dim rng As Range, vRng As Range
        Set vRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
        For Each rng In vRng
            If rng.Validation.Type = 3 Then
                If rng.Value = "" Then rng.Value = "-Select-"
            End If
        Next
    'Application.EnableEvents = True 'JDS added
    Select Case Target.Address
        Case "$G$2"
            Range("G8").ClearContents
        Case "$H$2"
            Range("H8").ClearContents
        Case "$I$2"
            Range("I8").ClearContents
        Case "$J$2"
            Range("J8").ClearContents
    End Select
End Sub

As for the conditional formatting on the entire worksheet, select the entire sheet by clicking on the box in the upper left hand corner of the sheet (the box where the column letters and row number intersect) and repeat the steps you took to add conditional formatting to G8:J8.

 

[JStirfry]

Thank you! Rather than specifying exact locations (like you did with "Case "$G$2" Range("G8").ClearContents"), is there a way to apply this philosophy across the "Used Range" like you did with the first Code you wrote for me?

Here's a little background on what my end goal is:
For the final workbook I'm developing, I intend on using this for a worksheet that will contain a form to be filled out that documents items within rooms in a building. Each form will be for a single room. I plan to copy that form within the same worksheet, as many times as there are rooms in the building, and then have a button with a macro assigned to it that will allow me to "reset" all cells within a highlighted area (or selection) that contains ONLY data validation drop downs (I will more than likely need help with this too!).

I'm trying my best to automate things for convenience. With that being said, if I don't have to, I don't want to go into the VBA code every time I create a new room and specify the exact cell locations in the code.

I hope I'm being clear enough regarding my intentions. Thanks again!

 

[mumps]

A macro that works with a sample file most often will not work with the final version of the file. It would be easier to help if you could do a manual mockup of a sample that is representative of the final file and explain in detail what you want to do referring to specific cells, rows, columns and sheets.

 

[JStirfry]

I embedded comments into this file:
https://drive.google.com/file/d/1Ua-kRaGm_STtL09ceUPWWDZxXSCZEtnQ/view?usp=sharing

Greatly appreciated!

 

[mumps]

This macro should take care of your first question:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim rng As Range, vRng As Range
    Set vRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
    For Each rng In vRng
        If rng.Validation.Type = 3 Then
            If rng.Value = "" Then rng.Value = "-Select-"
        End If
    Next
    If Target.Row = 9 Then
        Cells(12, Target.Column).ClearContents
        Cells(16, Target.Column).ClearContents
        Cells(19, Target.Column).ClearContents
    End If
    Application.ScreenUpdating = True
End Sub

Regarding the other questions:

2. What do you mean by "random SELECTED area of cells"? Can you give an example?

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?

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