Home

Issue with LIST values in excel drop down

%3CLINGO-SUB%20id%3D%22lingo-sub-881254%22%20slang%3D%22en-US%22%3EIssue%20with%20LIST%20values%20in%20excel%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881254%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20restrict%20the%20values%20for%20input%20cells%20against%20set%20of%20values.%20Excel%20validates%20if%20I%20enter%20manually%20in%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF6600%22%3EBut%20it%20accepts%20invalid%20values%20if%20we%20apply%20%22%20FILL%20SERIES%20%22%20option%3C%2FFONT%3E.%3C%2FSTRONG%3E%20Could%20you%20please%20suggest%20me%20here%20how%20can%20I%20achieve%20to%20restrict%20invalid%20values%20at%20any%20cost.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20below%20image%20for%20details.%3C%2FP%3E%3CP%3EAlso%20attached%20sample%20excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-881254%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881309%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20LIST%20values%20in%20excel%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416966%22%20target%3D%22_blank%22%3E%40koradaramjee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20there%20are%20some%20flaws%20with%20data%20validations.%3C%2FP%3E%3CP%3ETo%20handle%20this%20scenario%20you%20can%20apply%20a%20conditional%20formatting%20rule%20in%20the%20column%20A%20so%20that%20if%20a%20value%20is%20entered%20somehow%20which%20is%20not%20available%20in%20the%20dropdown%20list%20would%20highlight%20the%20cell%20say%20with%20red%20color%20to%20visually%20alert%20the%20user%20that%20an%20invalid%20value%20is%20there%20in%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20other%20workaround%20is%20VBA%20which%20can%20stop%20this%20to%20take%20place%20and%20only%20values%20in%20the%20dropdown%20can%20be%20entered%20in%20the%20column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached%20with%20the%20following%20Change%20Event%20code%20on%20Sheet1%20Module%20which%20will%20only%20allow%20a%20user%20to%20choose%20a%20valid%20entry%20only%20from%20the%20dropdown%20list%20in%20a%20cell%20if%20that%20cell%20has%20a%20dropdown%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20n%20As%20Variant%0ADim%20cel%20As%20Range%0ADim%20tbl%20As%20ListObject%0ASet%20tbl%20%3D%20ListObjects(%22Table1%22)%0AIf%20Target.Column%20%3D%201%20And%20Target.Row%20%26gt%3B%201%20Then%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20For%20Each%20cel%20In%20Target%0A%20%20%20%20%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%20%20%20%20n%20%3D%20cel.Validation.Type%0A%20%20%20%20%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20%20%20%20%20If%20n%20%3D%203%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20IsError(Application.Match(cel.Value%2C%20tbl.DataBodyRange.Columns(1)%2C%200))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cel.Value%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20cel%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881865%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20LIST%20values%20in%20excel%20drop%20down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20thanks%20for%20quick%20reply.%20it%20works%20by%20making%20the%20cell's%20value%20as%20blank%20through%20VBA.%3C%2FP%3E%3CP%3EIn%20my%20case%2C%20its%20downloaded%20excel(xlsx)%20from%20SAP%20system%20and%20user%20will%20modify%20the%20data%20and%20uploads%20.%3C%2FP%3E%3CP%3EI%20am%20developing%20these%20excels%20from%20Open%20XML%20and%20merge%20with%20SAP%20ABAP%20programs.%3C%2FP%3E%3CP%3EI%20will%20check%20with%20business%20if%20they%20are%20Ok%20with%20xlsm%20type%20.%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20advise%20me%20if%20there%20is%20any%20option%20with%20openXML%20without%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
koradaramjee
New Contributor

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

 

2 Replies

@koradaramjee 

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

 

 

@Subodh_Tiwari_sktneer 

 

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?

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies