DATA Validation Multiselect and Prevent Copy Paste in Data Validation

Delhi_IT

New Member
Joined
Mar 15, 2018
Messages
3
Have used a Multi Select Data Validation List in excel using the Below code available.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 26 Or Target.Column = 4 Or Target.Column = 6 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Now I want restrict the users from copy pasting data in those data validation ranges using the VBA code. However not able to. Have tried few codes available on forum.

Every time use a code Compile error is shown.

"Ambiguous Name Detected: Worksheet_Change".

I am new to VBA and any help in this regard would be appreciated.

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

severynm

Active Member
Joined
Jan 8, 2021
Messages
335
Office Version
  1. 365
Platform
  1. Windows
Every time use a code Compile error is shown.

"Ambiguous Name Detected: Worksheet_Change".
That would suggest you have multiple functions called Worksheet_Change in the worksheet module. A worksheet cannot have multiple functions with the same name - otherwise how would it know which one to run?
 

Delhi_IT

New Member
Joined
Mar 15, 2018
Messages
3
Thanks severynm for your reply, so what should I do to avoid that? As I mentioned i am New to VBA and would appreciate if you can help me with placing the two vba codes correctly. Thanks
 

severynm

Active Member
Joined
Jan 8, 2021
Messages
335
Office Version
  1. 365
Platform
  1. Windows
As I said, you are only allowed to have one. That's just something you'll have to be aware of in the future, though its not really the end of the world because as you found, the compiler will notify you each time. Look through your code to find the other one and figure out what to do - either replace one or the other or combine them together somehow.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,137
Messages
5,835,611
Members
430,370
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top