PDA

View Full Version : Drop down query



sujittalukde
05-18-2007, 12:28 AM
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.

Bob Phillips
05-18-2007, 01:39 AM
Uncheck the Ignore Blank box in the DV dialog.

sujittalukde
05-18-2007, 02:50 AM
Thats not solving my problem

zv735
05-18-2007, 03:08 AM
Data\Validation\List is Limit power
U cannot force users by validation but is posible by VBA

sujittalukde
05-18-2007, 04:20 AM
Could u provide the VBA? It will be helpful.thanks

zv735
05-18-2007, 07:16 AM
it's not perfect VBA

see att. file


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

lucas
05-18-2007, 08:25 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.

zv735
05-18-2007, 07:33 PM
I edit my Code(See First post)

Please, See and tall me it work or not

Thank you

lucas
05-18-2007, 07:38 PM
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.

sujittalukde
05-18-2007, 09:24 PM
THanks to all who replied to my qwery

zv735
05-19-2007, 01:50 AM
Thank you Lucas also