Resetting drop down listings using key strokes (macro)

Hi Guys,

Is it possible to use key-strokes (i.e a macro) to reset a drop down box to its original display?  Everytime I create a macro using this technique the listing display remains the same and doesn't revert back.  I have about 5 drop downs and would like to reset all once a button is selected at the bottom of my application.

Cheers,

Baron77

What do you mean by original display, and what sort of dropdown is it?

It's a list from Data Validation (i normally use combo boxes but haven't for this exercise)

At the top of my list, I have "Select A Postcode" (this is what shows by default in the cell).  When the user selects the cell, they can choose a PC from that list.  I would like to create a macro that will reset back to "Select A Postcode"... Many thanks

Okay, all you need in your macro is (Changing the cell to suit)

Public Sub ResetDV()

    With Range("C5")
    
        If .Validation.Type = xlValidateList Then
        
            .Value = Split(.Validation.Formula1, ",")(0)
        End If
    End With
End Sub

Thanks for assisting with this.  I have transposed this into Visual basic but have noticed that this makes the cell i.e. C5 a blank entry (apologies for not being clearer).  Can i reset the C5 to the 1st item in the listing (in my case - "Select Postal Code").  For clarification, the relevant postal codes would be the 2nd, 3rd, 4th items etc.  As I have about 5 additional cell's that have been data validated also (i.e. drop downs), I really need to make these reset too (in sync with the others).  Could you show me the code please? Let's assume that they are cells C5, C10, C15, C20, C25.  Really appreciate your time - it's much appreciated, Baron77

You were clear, I understood. It doesn't return a blank for me, it returns the first item. That suggsest to ne that your list starts with a blank, not the Select text.

This extends it to the other cells.

Public Sub ResetDV()
Dim cell As Range

    For Each cell In Range("C5,C10,C15,C20,C25")
    
        If cell.Validation.Type = xlValidateList Then
        
            cell.Value = Split(cell.Validation.Formula1, ",")(0)
        End If
    Next cell
End Sub

Oh dear, that doesn't appear to be working either.

For clarification, this is the data stored in cells M1:M4

M1 - "Please select Postal Code"

M2 - SE1 XXX

M3 - SE2 XXX

M4 - SE3 XXX

CELLS A1, A3 & A5 contain the data validation (i.e. the drop downs containing M1:M4)

Each time I click the macro button, this happens:

A1 - "Please select Postal Code"

A3 - SE2 XXX

A5 - SE4 XXX

As you know, I would like all A cells to read "Please select Postal Code".

I owe you for your trouble!

Cheers,

Baron77

Aah! The critical bit of info, the list is in a range of cells

Public Sub ResetDV()
Dim cell As Range

    For Each cell In Range("A1,A3,A5")
    
        If cell.Validation.Type = xlValidateList Then
        
            cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
        End If
    Next cell
End Sub

Life Saver!  Thank you very much.

For this macro to always work, would the range(s) always need to be on the same page as the drop down(s)?

No, if it were separate sheets, you would need to process independently, maybe a loop like so

Public Sub ResetDV()
Dim ws As Worksheet
Dim cell As Range

    For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
    
        For Each cell In ws.Cells.SpecialCells(xlCellTypeAllValidation)
    
            If cell.Validation.Type = xlValidateList Then
            
                cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
            End If
        Next cell
    Next 

No, if it were separate sheets, you would need to process independently, maybe a loop like so

Public Sub ResetDV()
Dim ws As Worksheet
Dim cell As Range

    For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
    
        For Each cell In ws.Cells.SpecialCells(xlCellTypeAllValidation)
    
            If cell.Validation.Type = xlValidateList Then
            
                cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
            End If
        Next cell
    Next 

Cheers for clarifying Bob