Through Data\Validation\List a drop down menu is created items are a,b,c,d. Normal excel feature allows to delete the cell and the cell can be kept blank.I want that excel will force users to select from the drop down list and the cell cannot be kept blank.
Uncheck the Ignore Blank box in the DV dialog.
Thats not solving my problem
Data\Validation\List is Limit power
U cannot force users by validation but is posible by VBA
Could u provide the VBA? It will be helpful.thanks
it's not perfect VBA
see att. file
[VBA]
Dim OldAdd As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Range
For Each C In [inputcell]
If C.Address = OldAdd Then
If Range(OldAdd) = "" Then
MsgBox "Cannot Blank"
Application.EnableEvents = False
Range(OldAdd).Select
Application.EnableEvents = True
Exit Sub
End If
End If
Next C
OldAdd = Target.Address
End Sub
[/VBA]
Last edited by zv735; 05-18-2007 at 08:34 AM.
Who is deleting them...if it's users and you don't want them to you could put your list on a hidden sheet and use a range name for the list..
The only other way I know of would be if you have a sheet for your list and you need users to have access to it you could use a sheet change event to make the range dynamic and then delete any rows that are blank in that column.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I edit my Code(See First post)
Please, See and tall me it work or not
Thank you
Hi zv,
your code does alert if you use a blank and sets focus back to the cell. I don't think you will do better than that with such a small amount of code but I've been proven wrong many times...nice one.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
THanks to all who replied to my qwery
Thank you Lucas also
Posting Permissions
Reply With Quote
