Access 97 drop down list

[ddiamond]

I have created a drop down list in excel 97 to restrict entry to a fixed set of values. This restriction works fine as long as the user either uses the mouse to select a value from the drop down, or the user manually types in a value. The problem is that the user can use copy and paste to put what ever value he wants into the cell while by-passing the validation. How can I prevent the user from side-stepping the validation with copy and paste? Can this be done with VBA?

 

[PHV]

The topic says Access 97
The post says excel 97
So, which application and, more important, which kind of control: access combo, userform combo, activeX combo, ... ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Assuming from your post in Forum68, you are referring to a Data > Validation in Excel.

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

that way they can copy-paste EXCEPT to the validation range.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ddiamond]

Thanks Skip. I don't think that the selection_change is the correct event. It is not firing when I paste a value into cell with validation. It does fire as soon as I select that cell however.

 

[SkipVought]

"It is not firing when I paste a value into cell with validation."

It does NOT fire on the CHANGE.

"It does fire as soon as I select that cell however."

YES! I prevents a PASTE in the ValidationRange. Of course, you have to supply that reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ddiamond]

The following code is not preventing pasting into cell B5.

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

The statement Application.CutCopyMode = False is executing when cell B5 is selected, but this statement is not preventing a subsequent paste. What am I doing wrong?

 

[SkipVought]

The only "hole" that I've found is if B5 is not selected, but is pasted over from a multiple cell copy. This could be thwarted by preventing multiple selections on that sheet.

  If Target.Count > 1 Then
      Target(1).Select
  End If

If you copy a selection and then select B5, the copy is lost and nothing can be pasted. It's that simple.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ddiamond]

I'm not even copying a whole cell. After entering edit mode (F2) I am selecting the text from my source cell, copying that selected text, and pasting it into my target cell (B5). The worksheet is allowing the paste eventhough CopyCutMode = false.

 

[ddiamond]

I think I see the problem. Setting CutCopyMode to false prevents you from pasting an entire cell over top B5, but it does not prevent you from editing B5 and pasting copied text into B5. Is it possible to prevent the user from pasting raw text into the cell? Basically, I want to force the cell to be one of the values in my validation list.

 

[SkipVought]

Thank you for supplying SPECIFIC information. One normally does not consider a copy done within a cell when referring to Copy 'n' paste. This withheld tidbit might have saved you the days between your original post and now.

Try something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, t As Range, bFound As Boolean
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        For Each t In Target
            bFound = False
            For Each r In [mlist]
                If r.Value = t.Value Then
                    bFound = True
                    Exit For
                End If
            Next
            If Not bFound Then t.ClearContents
        Next
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}