Display default value on delete

[sandhya16]

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

 

[jaslake]

Hi sandhya16

I've not used File Sharing drop boxes before so I'm not certain which link you need. Having said that, it might make more sense for you to upload your File so I can see why the Code is not working for you

http://<a href=http://www.filedropper.com/book3><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file upload storage</a></div>

http://www.filedropper.com/book3

 

[sandhya16]

Can you give your email id?

Also i am not looking for a check form button i dont want to add any button for it.

Hi sandhya16

I've not used File Sharing drop boxes before so I'm not certain which link you need. Having said that, it might make more sense for you to upload your File so I can see why the Code is not working for you

http://
file upload storage


http://www.filedropper.com/book3

 

[jaslake]

Hi sandhya16

Were you able to download my File from the Drop Box? If so, did the Code work as expected?

I'll send my email address via Private Message.

 

[sandhya16]

Yes this is what i was looking for.

Thank you for your help.

Hi sandhya16

Were you able to download my File from the Drop Box? If so, did the Code work as expected?

I'll send my email address via Private Message.

 

[jaslake]

You're welcome...glad I could help.

 

[sandhya16]

When i am selecting the whole range from A2:A10 and pressing delete then the default value "select value" is not getting displayed.its coming on single cell deletion..what changes are to be made for whole range?

 

[jaslake]

Hi sandhya16

Try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cel          As Range
   '   If Target.Cells.Count > 1 Then Exit Sub
   If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
      For Each cel In Range("A2:A10")
         Application.EnableEvents = False
         If IsEmpty(cel.Value) Then cel.Value = "Select Value"
      Next cel
   End If
   Application.EnableEvents = True
End Sub

 

[sandhya16]

it worked

thank you

When i am selecting the whole range from A2:A10 and pressing delete then the default value "select value" is not getting displayed.its coming on single cell deletion..what changes are to be made for whole range?