| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate issue
I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Steven,
You can use the selection change event for the sheet. For example, the code below will protect the Data Validation in cells B2:B100. Copy the code, right-click the sheet tab, select "View Code" and paste the code in the window that appears. Note that you may want to let the users know why they can't paste after selecting one of those cells.... HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub Application.CutCopyMode = False End Sub "Steven" wrote in message ... I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Brilliant! Thank you.
"Bernie Deitrick" wrote: Steven, You can use the selection change event for the sheet. For example, the code below will protect the Data Validation in cells B2:B100. Copy the code, right-click the sheet tab, select "View Code" and paste the code in the window that appears. Note that you may want to let the users know why they can't paste after selecting one of those cells.... HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub Application.CutCopyMode = False End Sub "Steven" wrote in message ... I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
This is very nearly what I'm looking for!
The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Pablo,
Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library....
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Hi Bernie,
This is looking good so far but I'm not sure what you mean about the MS Forms 2.0 Object Library?? How do I make that bit work? "Bernie Deitrick" wrote: Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Pablo,
In the VBE, with the project active, use Tools / References, and look for "Microsoft Forms 2.0 Object Library", and put a check next to it. HTH, Bernie MS Excel MVP "pablo bellissimo" wrote in message ... Hi Bernie, This is looking good so far but I'm not sure what you mean about the MS Forms 2.0 Object Library?? How do I make that bit work? "Bernie Deitrick" wrote: Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Bernie - Thats great!
The next enhancement to this would be to allow users to paste more than one value at a time. If a user has a list containing 5 values and they all meet the validation rule then allow the paste. If one of the values is incorrect then just cutcopy= false will be fine. If you are bored with this now I'll fully understand! Your help this far is greatly appreciated! Thanks "Bernie Deitrick" wrote: Pablo, In the VBE, with the project active, use Tools / References, and look for "Microsoft Forms 2.0 Object Library", and put a check next to it. HTH, Bernie MS Excel MVP "pablo bellissimo" wrote in message ... Hi Bernie, This is looking good so far but I'm not sure what you mean about the MS Forms 2.0 Object Library?? How do I make that bit work? "Bernie Deitrick" wrote: Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Pablo,
Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject Dim AllVar As Variant Dim i As Integer If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() AllVar = Split(SomeVar, Chr(13) & Chr(10)) For i = LBound(AllVar) To UBound(AllVar) If AllVar(i) < "" Then If Left(AllVar(i), 2) < "07" Or Len(AllVar(i)) < 11 Then Application.CutCopyMode = False End If End If Next i End Sub "pablo bellissimo" wrote in message ... Bernie - Thats great! The next enhancement to this would be to allow users to paste more than one value at a time. If a user has a list containing 5 values and they all meet the validation rule then allow the paste. If one of the values is incorrect then just cutcopy= false will be fine. If you are bored with this now I'll fully understand! Your help this far is greatly appreciated! Thanks "Bernie Deitrick" wrote: Pablo, In the VBE, with the project active, use Tools / References, and look for "Microsoft Forms 2.0 Object Library", and put a check next to it. HTH, Bernie MS Excel MVP "pablo bellissimo" wrote in message ... Hi Bernie, This is looking good so far but I'm not sure what you mean about the MS Forms 2.0 Object Library?? How do I make that bit work? "Bernie Deitrick" wrote: Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validate issue
Oh, you're good!
Thanks for this. "Bernie Deitrick" wrote: Pablo, Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject Dim AllVar As Variant Dim i As Integer If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() AllVar = Split(SomeVar, Chr(13) & Chr(10)) For i = LBound(AllVar) To UBound(AllVar) If AllVar(i) < "" Then If Left(AllVar(i), 2) < "07" Or Len(AllVar(i)) < 11 Then Application.CutCopyMode = False End If End If Next i End Sub "pablo bellissimo" wrote in message ... Bernie - Thats great! The next enhancement to this would be to allow users to paste more than one value at a time. If a user has a list containing 5 values and they all meet the validation rule then allow the paste. If one of the values is incorrect then just cutcopy= false will be fine. If you are bored with this now I'll fully understand! Your help this far is greatly appreciated! Thanks "Bernie Deitrick" wrote: Pablo, In the VBE, with the project active, use Tools / References, and look for "Microsoft Forms 2.0 Object Library", and put a check next to it. HTH, Bernie MS Excel MVP "pablo bellissimo" wrote in message ... Hi Bernie, This is looking good so far but I'm not sure what you mean about the MS Forms 2.0 Object Library?? How do I make that bit work? "Bernie Deitrick" wrote: Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Pablo, Try this: the 13 (rather than 11) is due to extra control characters in the text string returned from the clipboard. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SomeVar As String Dim MyDataObj As New DataObject If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Application.CutCopyMode = False Then Exit Sub MyDataObj.GetFromClipboard SomeVar = MyDataObj.GetText() If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub Application.CutCopyMode = False End Sub "pablo bellissimo" wrote in message ... This is very nearly what I'm looking for! The code below will just prevent a user pasting into a cell that has has validation rules applied to it. I would like to allow a user to paste a value as long as it meets my data validation rules. I only want users to be able to enter a number which is 11 digits long and it must start with "07". I'm sure this is possible but I've not yet found anyone able to help. please please please try to offer me a solution - I'm getting desperate! Thanks:) "Steven" wrote: I am using cell validation. I have noticed if I copy a cell from a different area on the sheet that does not have validation it will take the validation away from my original cell. Why is this? How can I make it so a user cannot wipeout the validation. Thank you for your help. Steven |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| validate | Excel Discussion (Misc queries) | |||
| validate | Excel Programming | |||
| Looking up a name and then being able to validate it | Excel Worksheet Functions | |||
| VALIDATE WB NAME | Excel Programming | |||
| How to validate | Excel Worksheet Functions | |||
Linear Mode
