Drop Down List to Allow Exactly What is on a Cell

[Diablo527]

Hello, so I have a problem. We use a document to update and save information when employees are assigned a specific task. There is one User who does not know how to follow directions and messes the aesthetics of the document. What I am trying to achieve is to force the User(s) to select from the drop down list using the Data Validation option. I have ran into a few issues. When I use the List option, I can choose a range of cells with the information needed, the problem is that the User instead of selecting from the drop down menu, she types over it and does not capitalize thus damaging the aesthetics. So in the attached document, instead of selecting "Low" the User will type "low". I was also able to select the List option and in the Source box type in the values without the "=" sign preceding it. This actually worked but the problem is that I have about 30 names to type in the Source box and the limit of characters is 256 or so. I also looked into using the Custom option and using =COUNT(FIND(Cell,Name of Range)) as a Formula. Again, this worked, but the User will not know what to type in or what to select since there is no list to choose from. Any ideas or suggestions since this is driving me crazy? I wouldn't think it is such a difficult task but it has become one. Thank you in advance.

 

[PWD]

Well, that's weird because when I've downloaded the workbook it won't allow me to enter anything other than what's on the list.

Many thanks,
D€$

 

[PWD]

Interestingly, I can type whatever I like in another cell and paste that over A1.

Many thanks,
D€$

 

[strongm]

Sure, but in a case-insensitive fashion, so low, lOW, LoW, Low, LOW, loWare all allowed, which is the OPs point.

The normal way of fixing this is, as the OP mentions, to enter the list directly as the Source in the Data Validation dialog - but has discovered that the 254 character limit there is a problem.

I'm not aware of any easy fix to this. One method would be to attach some VBA to the worksheets Change event. Something like the following:

[blue]Option Explicit

Dim WithEvents MySheet As Worksheet
Private ChangeEvent As Boolean

Private Sub MySheet_Change(ByVal Target As Range)
    Dim myRange As Range
    If Not ChangeEvent Then [COLOR=green]' prevent reentry[/color]
        ChangeEvent = True
        On Error GoTo ErrorHandler
        If Target.Validation.Formula1 <> "" Then
            On Error GoTo 0
            Set myRange = Range(Replace(Target.Validation.Formula1, "=", ""))
            Target.Formula = myRange.Find(Target.Formula).Value
        End If
NoValidation:
    End If
    
    ChangeEvent = False
    Exit Sub
    
ErrorHandler:
    Resume NoValidation
End Sub[/blue]

 

[SkipVought]

this will keep the user from copying over the data validation cell...

In the Sheet Module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.CutCopyMode = False
    End If
End Sub

 

[Diablo527]

StrongM, you actually hit it on the nail with understanding the issue at hand. I will give your suggestion a shot and play around with the code provided. If you think of anything else as another option, it would be greatly appreciated. Again, thank you.

 

[1726]

An alternative tactic:

Use conditional formatting to hide the cell contents or display an error message unless it is an exact match for an item in your drop down list.

Select the cell(s) where your user tries to type an entry, and create a new conditional formatting rule with 2 aspects to it.

Part 1:Use this formula in the Conditional Format:

[tt]=NOT(EXACT(A1,VLOOKUP(A1,YourListSource,1,FALSE)))[/tt]

the VLOOKUP function will find an entry matching the users typed entry in your list
the EXACT function will compare if they have the same case
the NOT function reverses the logic so the formula gives a TRUE response when the user has typed the wrong case.

Replace A1 with the co-ordinate of the cell your are formatting.
Replace YourListSource with the range containing your drop down list (using absolute cell references. e.g. $B$2:$B$50 or use a range name)

Part 2: Select a NUMBER format of Custom set to: [tt];;;[/tt]

This format (3 semicolons) will hide the contents of the cell from the screen and printer - it will still be visible in the formula bar.

To display an error message (that needs to be short enough to fit in the cell) use: [tt];;;"SELECT FROM LIST"[/tt]

You could combine either of these with a nice red fill colour to make it stand out when the user has typed the wrong case.

Hopefully your use will realise that typing the incorrect case does not work!

Cheers

 

[Diablo527]

1726,

So it seems that your idea would work best (thanks StrongM & SkipVought but I really know little about VBA to be quite honest in order to try your suggestions but it is greatly appreciated). The only problem is that I am lost on Part 2 of your suggestion. Here is what I have done so far:
For testing purposes I will create this on 1 sheet (once this has been "mastered", I will create unto another sheet and hide it), on range A1:A3 there are the options: Low, Medium, & High as it needs to be typed. On B1, selected the Conditional Formatting option, New Rule, Use a formula to determine which cells to format, and added the formula that you recommended: =NOT(EXACT(B1,VLOOKUP(B1,$A$1:$A$3,1,FALSE))). Then I clicked Format on the Format Cells box, selected Number, Custom, then typed the three semi-colons as suggested. Then I am totally lost at that point. I have attached the doc. if you would like to look over or even revise it. Thanks.

 

[1726]

Diablo527

I have looked at your test workbook and it looks fine - except it is missing the Data Validation drop down list that you started with.

Even as is, if you type High, Medium or Low - into B1 it will display the entry - if you type high, medium or low it will be hidden.
Now use your data validation to prevent anything else from being entered and to provide the drop down list.

Notes:
If you place your list from A1:A3 on another hidden sheet you may need to use a range name to identify it - otherwise the data validation rule will not work in older versions of Excel.

You should bear in mind that this method is not entirely fool proof - it is just added 'persuasion'.
[ul][li]It does not stop the user typing entries with the wrong case - it just hides them or displays an error message.[/li]
[li]Anyone with the right knowledge can remove these settings.[/li]
[li]And, as PWD, discovered the settings can be overwritten by copying and pasting an entry from an unprotected cell.[/li]
[/ul]

cheers

 

[1726]

Diablo527
I have attached a revised version of your test worksheet with 3 variations of my Conditional Formatting in cells B1,C1 & D1.
Try typing entries in the wrong case into these cells

Cheers

 

[SkipVought]

VBA is the only way to assure 1) correct case & prevent over-paste!

 

[Diablo527]

Thanks, gentlemen, it is greatly appreciated!

 

[Andrzejek]

You should show appreciation for help received by clicking on the link “Great Post? Star it” at the helpful post(s). This also let others know which post(s) were beneficial to solution of your problem.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

>I really know little about VBA

Just copy and paste the following (very slightly modified) code into ThisWorkbook module, and that should be all you need to do ...

[blue]Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Static ChangeEvent As Boolean
    Dim myRange As Range
    Dim OriginalText As String
    If Not ChangeEvent Then [green]' prevent reentry[/green]
        ChangeEvent = True
        On Error GoTo ErrorHandler
        If Target.Validation.Formula1 <> "" Then
            OriginalText = Target.Formula
            Set myRange = Range(Replace(Target.Validation.Formula1, "=", ""))
            If Not myRange.Find(Target.Formula) Is Nothing Then
                Target.Formula = myRange.Find(Target.Formula).Value
            End If

        End If
NoValidation:
    End If
    
    ChangeEvent = False
    Exit Sub
    
ErrorHandler:
    Resume NoValidation
End Sub[/blue]

 

[combo]

First of all, sorry for coming with new idea after a week from starting this thread.
If I need a strictly defined input from the list, sometimes I use protected workbook with forms (no activex!) comboboxes. The structure:
1) data source: hidden (or very hidden) worksheet with named list and combo box selection value, also named cell,
2) input sheet, protected: forms comboboxes with lists and linked cells referring to named ranges in datasource worksheet, controls are set not to be printed. Below the control, in a cell I have a formula: =INDEX(ListName,ListLinkedCell), where ListName and ListLinkedCell are ranges associated with the control.
As a consequence, on the screen user can select only values from the list, in the printout there is no control, but the cell below. As it contains formula, it can be protected too.

combo