Diaplay text in a cell

[sujittalukde]

In the attached WB I want that a value shall always be present at a cell . Can this be done? More wxplanations are given at the WB?

[Bob Phillips]

Why not just pre-load the cell with that value?

[sujittalukde]

Since I am using the list by valiadtion list, this may be subject to deletion. THis led some user to skip the cell which I experienced. So I wnat that a value "Select from the list" should always be appear in the cell.

[Bob Phillips]

Then that would need VBA, or don't allow deletions.

[sujittalukde]

How can I restrict deletion? or Could the VBA be provided?

[Bob Phillips]

Restrict deletion by just unchecking the Ignore Blank box in the DV.

[sujittalukde]

I have unchecked the Ignore blank box in data validation, but deletion is still taking place and the cell goes blank.

[rory]

Right-click the sheet tab, choose View Code and then paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B3]) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
If Len([B3]) = 0 Then [B3] = "--SELECT FROM LIST--"
Application.EnableEvents = True
End Sub


HTH
Rory

[sujittalukde]

Thank you Rory, this is what I was looking for. Many Many thanks for the code.