Solved

Drop down list that cannot be copied over

Posted on 2011-02-12
25
355 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
  • 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
 
LVL 41

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 41

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 41

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 41

Expert Comment

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

Dave
0
 
LVL 41

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 41

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 41

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 41

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 41

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 41

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 41

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 41

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 41

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now