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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jaslake

Board Regular
Joined
Mar 28, 2009
Messages
119
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
 

Shupert

New Member
Joined
Aug 18, 2015
Messages
24
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.
 

sandhya16

New Member
Joined
Jun 8, 2015
Messages
34
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
 

jaslake

Board Regular
Joined
Mar 28, 2009
Messages
119
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:

Shupert

New Member
Joined
Aug 18, 2015
Messages
24
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.
 

sandhya16

New Member
Joined
Jun 8, 2015
Messages
34
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.
 

sandhya16

New Member
Joined
Jun 8, 2015
Messages
34
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.
 

jaslake

Board Regular
Joined
Mar 28, 2009
Messages
119
Hi sandhya16

I don't believe this Forum has that capability...if it does. please educate me.
Can you please attach the excel you tried this code on..
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,106
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top