Sep 29 2019 06:34 AM
Hello Experts,
I am trying to restrict the values for input cells against set of values. Excel validates if I enter manually in the cell.
But it accepts invalid values if we apply " FILL SERIES " option. Could you please suggest me here how can I achieve to restrict invalid values at any cost.
Please see below image for details.
Also attached sample excel
Sep 29 2019 08:19 AM
Yes, there are some flaws with data validations.
To handle this scenario you can apply a conditional formatting rule in the column A so that if a value is entered somehow which is not available in the dropdown list would highlight the cell say with red color to visually alert the user that an invalid value is there in the cell.
Or other workaround is VBA which can stop this to take place and only values in the dropdown can be entered in the column A.
Please refer to the attached with the following Change Event code on Sheet1 Module which will only allow a user to choose a valid entry only from the dropdown list in a cell if that cell has a dropdown in it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant
Dim cel As Range
Dim tbl As ListObject
Set tbl = ListObjects("Table1")
If Target.Column = 1 And Target.Row > 1 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cel In Target
On Error Resume Next
n = cel.Validation.Type
On Error GoTo 0
If n = 3 Then
If IsError(Application.Match(cel.Value, tbl.DataBodyRange.Columns(1), 0)) Then
cel.Value = ""
End If
End If
Next cel
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Sep 29 2019 10:13 PM
Really thanks for quick reply. it works by making the cell's value as blank through VBA.
In my case, its downloaded excel(xlsx) from SAP system and user will modify the data and uploads .
I am developing these excels from Open XML and merge with SAP ABAP programs.
I will check with business if they are Ok with xlsm type .
Also advise me if there is any option with openXML without VBA?