Drop down list that cannot be copied over

Not sure what you want...

You want in cell B2 that only the values 0, 1, or - can be selected?

jppinto

If you want to avoid copy and paste into this cell, you need to protect your sheet!

jppinto

If you're using Validation Lists, take a look at my EE article about "Cascading Validation Lists":

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_4454-Cascading-Validation-Lists.html

Hope that it's useful for your work... :)

jppinto

Tavasan65 -  here's your solution -

To see more on how its done, Read this:  http://www.j-walk.com/ss/excel/tips/tip98.htm

I took your sheet, created a couple validation cells (yours + one more)  Then I defined a name "ValidationRange" to include those cells, after I selected them, scope that worksheet.

Then, in the CODEPAGE for Sheet1, I put the function (reference tip, above):

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub


Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

Select allOpen in new window

Note, I included some code to disable then re-enable events (otherwise possible infinite recursion).

application.enableevents = false

change the cell back with application.undo

then application.enableevents = true

Its all in the attached.

You could also do this for the entire workbook, but I kept it simple for now.  Let me know.

Enjoy!

Dave
CopyPaste-sample-r1.xlsm

Hey Dave,

when I open the sample and try to change the value in cell B2 it is locked.  How can I unlock it and enable it.

what do you mean it is locked?  what message do you get?

The worksheet is NOT locked

Dave

I think I might know what error you're getting.  The validation control gives you and error and undoes the action, THEN my code kicks in.

I think I've handled it with the on error resume next command.


Try this - if not working, please be specific about what you did.  Its fairly straightforward, so hopefully all ok.

Dave
CopyPaste-sample-r2.xlsm

I open the file and when I click on cell B2 to change the current value from 1 to 0 I get this error message.  " Your last operation was cacened.Itwould have deleted datea valation rules."  So I guess once you make a selection the cell is locked from making any changes.  What I hoping for was to simply avoid cutting and pasting data into that cell.

let me check - that's not what I'm getting...

Dave

This is great.  I'm a novice at programming.  How do I change the validation range?  For example from all cells in column B to all cells in column D and column F?

Hit help on defining names

Or alt E D N

Or find the formula ribbon look for names

You use that interface to define the name

Let me know if you need more assistance

Dave

You can automatically set the validation range with this code:

Further to my last comment, you can pull up Excel Help and go to names, or create name/modify name to learn more how to do this manually.

But, you may have lots of cells requiring this setting...  Here's the code that sets the validation range on the active sheet

Dim rng As Range
    Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
    ActiveWorkbook.Names("ValidationRange").RefersTo = rng

In the attached, I put it in the ThisWorkbook module, during Workbook_Open event.  So the ValidationRange is set, and can't be changed by the user.

I also created the macro SetValidationRange() that has this code.

See attached & Enjoy!

Dave
CopyPaste-sample-r4.xlsm

If you want this to function for the entire workbook (re: on multiple sheets), then do this:



ThisWorkbook module has Workbook_Open() routine - to define all ValidationRange names in each sheet
Workbook_SheetChange() routine to test for change in validation range and UNDO if needed (re: copy/paste over)
and the function HasValidation() to test for range being pasted into having a validation before paste or not.

See below:

Private Sub Workbook_Open()
Dim mySheet As Worksheet

    For Each mySheet In Application.Worksheets
        Call SetValidationRange(mySheet.Name)
    Next mySheet

End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Does the validation range still have validation?
    On Error Resume Next
    If HasValidation(Sh.Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        On Error Resume Next
        Application.Undo
        On Error GoTo 0
        Application.EnableEvents = True
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
    On Error GoTo 0
End Sub
Private Function HasValidation(r) As Boolean
Dim notValid As Boolean
Dim myCell As Range

    IsValid = True
    
    ' Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    For Each myCell In r
        x = myCell.Validation.Type
        If Err.Number <> 0 Then IsValid = False
    Next myCell
    On Error GoTo 0
    HasValidation = IsValid
End Function

Select allOpen in new window

Then, there's a MODULE that has the code SetValidationRange(wksheet) and ActiveSetValidationRange().  They both do the same operation, except the SetValidationRange routine works with the Workbook_Open routine - to hit every sheet.  The ActiveSetValidationRange() routine is for you to run on a particular sheet to see behavior...

See below:

 

Sub SetValidationRange(mySheet As Worksheet)
Dim rng As Range

    Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error Resume Next
    ActiveWorkbook.Worksheets(mySheet.Name).Names("ValidationRange").RefersTo = rng
    If Err.Number <> 0 Then 'assume the name needs to be created
        ActiveWorkbook.Worksheets(mySheet.Name).Names.Add Name:="ValidationRange", RefersTo:=rng.Address
    End If
    On Error GoTo 0
End Sub
Sub ActiveSetValidationRange()
Dim rng As Range

    Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error Resume Next
    ActiveWorkbook.Worksheets(ActiveSheet.Name).Names("ValidationRange").RefersTo = rng
    If Err.Number <> 0 Then 'assume the name needs to be created
        ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="ValidationRange", RefersTo:=rng.Address
    End If
    On Error GoTo 0
    
End Sub

Select allOpen in new window

See attached for this workbook put together as a whole:

Enjoy!

Dave
CopyPaste-sample-r5.xlsm

In the above post, the code should be changed to:

Private Sub Workbook_Open()
Dim mySheet As Worksheet

    For Each mySheet In Application.Worksheets
        Call SetValidationRange(mySheet)
    Next mySheet

End Sub


It is correct in the attachment I attached, but the displayed code needed this correction.

Dave

@Tavasan - are you ready to close this out, does the solution offered not work for you, or do you have other issues?

Dave

I have provided a viable solution, tested at my workstation.  I never saw a response after my last posting.

RE: comment http:#a34908207.html "this is great.. how do I" implies my solution was working for the OP.  If the OP doesn't respond, please use this as my recommendation for points to be granted.

If Tavasan doesn't respond, I would ask jppinto to opine and moderator to consider this as well.

Dave

sorry for the very late response.  I've been out of the country.

As I have stated before I have been out of the country and was unable to finalize the acceptance of dlmille solution ID: 34912070.  When I try to accept the solution I receive an error.  Please correct this issue so the dlmille can receive his/her points for this work.

Thank you.

Not to worry.  this will be closed out tomorrow with points awarded as you desire.  

Dave

sorry for the delay Dave and once again thank you.

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.