Display default value on delete

sandhya16

New Member
Joined
Jun 8, 2015
Messages
34
I have a range of data from A2:A10 that i am displaying through data validation drop down list.

I want to display a default value when i press delete on any of the cells in range .The default value -"Select Value" which will always be displayed when i am going to select a dropdown value.Please help.

eg-the dropdown should have value like this and select value will always be displayed even if i press delete or nothing is entered.

Select Value
A
B
C
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi sandhya16

Something like this in the Sheet Module should do as you require.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
      Application.EnableEvents = False
      If IsEmpty(Target.Value) Then Target.Value = "Select Value"
   End If
   Application.EnableEvents = True
End Sub
 
Upvote 0
I think I understand your intent. My solution to this is to use conditional formating that looks for when the cell value is empty, or equals "", and if so it fills the cell with a color, like a solid yellow. That tells people they need to input data in that cell until there is no more bright yellow cells. Then keep the data validation and remove the Select Value cell from the range (since the cell format has taken it's place) and you should be good. That was my work around anyway, you can make it so other cells would display that if the cell were empty but unfortunately a formula can't do that to the cell it is in, it makes a circular logic. Hope this helps.
 
Upvote 0
I tried this code its not working in my sheet.when i am pressing delete button in any of range -A2:A10 having dropdown data validation then its still empty.

I am not looking for conditional formatting.i think a vba code will do.



Hi sandhya16

Something like this in the Sheet Module should do as you require.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
      Application.EnableEvents = False
      If IsEmpty(Target.Value) Then Target.Value = "Select Value"
   End If
   Application.EnableEvents = True
End Sub
 
Upvote 0
Hi sandhya16

I've tested the Code and it works on my Test File.

Is "Select Value" one of the Validation Options?

Did you copy the Code into the Sheet Module? Right Click on the Sheet Tab, Select View Code...the Code should be in the right hand pane.

Go into VBA (Alt + F11)
Open the Immediate Window (Click on View, Select Immediate Window)
Type or Copy this "application.enableevents=true" into the Immediate Window (without the Quote Marks)...Click Enter.

Try it again...let me know.
 
Last edited:
Upvote 0
For a VBA code I will need the cells that you want the code to empty and replace with Select Value. You can then select all the cells in and have them write in Select Value. Provided that you have fixed cells you want to clear out. I put in some random cells to give you an idea of what it would look like. It would look like this:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A2").Select
Selection.Copy
Range("B2,B5,B6,B8").Select
Range("B8").Activate
ActiveSheet.Paste
Range("B3").Select
End Sub


You then attach that to a clear form button and it shold do what you need. If not just let me know.
 
Upvote 0
Thanks for you reply but i am not creating a clear form button here .its a data validation dropdown list from A2:A10 range and when i press delete from my keyboard then a default value-"Select Value" should appear.



For a VBA code I will need the cells that you want the code to empty and replace with Select Value. You can then select all the cells in and have them write in Select Value. Provided that you have fixed cells you want to clear out. I put in some random cells to give you an idea of what it would look like. It would look like this:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A2").Select
Selection.Copy
Range("B2,B5,B6,B8").Select
Range("B8").Activate
ActiveSheet.Paste
Range("B3").Select
End Sub


You then attach that to a clear form button and it shold do what you need. If not just let me know.
 
Upvote 0
Can you please attach the excel you tried this code on..

Thanks for you reply but i am not creating a clear form button here .its a data validation dropdown list from A2:A10 range and when i press delete from my keyboard then a default value-"Select Value" should appear.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top