Solved

Drop down list that cannot be copied over

Posted on 2011-02-12
25
360 Views
Last Modified: 2012-05-11
I have a spreadsheet that in cell B2 is a drop down list with choices.  In cell B1 is a date that is manually entered by user.  

What I want is that the only choices to be selected are within the drop down list and stop the ability of using the "copy and paste" into that cell.
CopyPaste-sample.xlsm
0
Comment
Question by:Tavasan65
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
  • 3
  • +1
25 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 34879325
Not sure what you want...

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

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34879331
If you want to avoid copy and paste into this cell, you need to protect your sheet!

jppinto
0
 
LVL 33

Expert Comment

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

http://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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 34879811
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

Open 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
0
 

Author Comment

by:Tavasan65
ID: 34883283
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34883586
what do you mean it is locked?  what message do you get?

The worksheet is NOT locked

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34883595
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
0
 

Author Comment

by:Tavasan65
ID: 34903163
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34903255
let me check - that's not what I'm getting...

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34903336
I'm so embarrassed.  I took a tip that I assumed work for > 1 cell in the HasValidation.  I guess they didn't test for multiple cells in the ValidationRange and NEITHER DID I.

I amended the code to look at all cells in the validation range to see if any had been changed.  It now appears to work properly with 1 to many cells in the ValidationRange.

as below:
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

Open in new window


It works like a champ for me, now.

Dave
CopyPaste-sample-r3.xlsm
0
 

Author Comment

by:Tavasan65
ID: 34908207
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?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34908699
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34911724
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34912070
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

Open 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

Open in new window


See attached for this workbook put together as a whole:

Enjoy!

Dave
CopyPaste-sample-r5.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34912080
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
0
 
LVL 42

Expert Comment

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35316877
I have provided a viable solution, tested at my workstation.  I never saw a response after my last posting.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35316960
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
0
 

Author Comment

by:Tavasan65
ID: 35397849
sorry for the very late response.  I've been out of the country.
0
 

Author Comment

by:Tavasan65
ID: 35397867
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35397878
Not to worry.  this will be closed out tomorrow with points awarded as you desire.  

Dave
0
 

Author Comment

by:Tavasan65
ID: 35401174
sorry for the delay Dave and once again thank you.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35406662
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question