VBA_change array to Range

[SP321]

Hi,

can anyone please update the code below? It is for dropdown menu, which cannot be overwritten with copy+paste.
This works perfectly, but sometimes the list is too long (over 255 characters) and once I close and open the file it is showing errors. I found the solution and it needs to be done through a range.

No matter how I try to change the code I get Syntax errors. Since I am complete beginner I can't figure out how to refer to a list in name manager.
Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range

Dim cell As Range

Dim dd As Variant

Dim i As Long

Dim mtch As Boolean

Dim msg As String

Dim myEntries As String

Set isect = Intersect(Range("B7:B600"), Target)

If Not isect Is Nothing Then

Application.EnableEvents = False

dd = Array("A", "B", "C", "D", "")

For Each cell In isect

mtch = False

For i = LBound(dd) To UBound(dd)

If cell.Value = dd(i) Then

mtch = True

Exit For

End If

Next i

If mtch = False Then

cell.ClearContents

msg = msg & cell.Address(0, 0) & ","

End If

Next cell

For i = LBound(dd) To UBound(dd)

myEntries = myEntries & dd(i) & ","

Next i

myEntries = Left(myEntries, Len(myEntries) - 1)

With Range("B7:B600").Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myEntries

' .IgnoreBlank = True

' .InCellDropdown = True

' .InputTitle = ""

' .ErrorTitle = ""

' .InputMessage = ""

' .ErrorMessage = ""

' .ShowInput = True

' .ShowError = True

End With

If Len(msg) > 0 Then

MsgBox "Invalid data in cells: " & vbCrLf & Left(msg, Len(msg) - 1), vbOKOnly, "Error"

End If

End If

Application.EnableEvents = True

End Sub

Can anyone please help? Thank you very much.

 

[James006]

Glad to hear at least the first step is solved ... get rid of the Array and use your list with a Named Range

With Excel, it is always safer to progress one step after the other ...

So your second step is to prevent a User from overwriting your Data Validation ... is that right ?

 

[SP321]

Glad to hear at least the first step is solved ... get rid of the Array and use your list with a Named Range

With Excel, it is always safer to progress one step after the other ...

So your second step is to prevent a User from overwriting your Data Validation ... is that right ?

understood yes, exactly

 

[James006]

OK ...
I understand you want the User to use your Data Validation List ...
But
would you like to allow the User to use Copy and Paste into your restricted area ... as long as the data copied can be found in your list ....
or
would you like to make sure any Copy and Paste is totally forbidden ...?

 

[SP321]

OK ...
I understand you want the User to use your Data Validation List ...
But
would you like to allow the User to use Copy and Paste into your restricted area ... as long as the data copied can be found in your list ....
or
would you like to make sure any Copy and Paste is totally forbidden ...?

Yes, the first and second option.
I would like for them to be able to use the drop down menu but also in case of copy paste to allow only the data in the drop down list

 

[James006]

Not sure to totally understand ...

However, you can test following macro

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rDataVal As Range
Set rDataVal = Range("B7:B600")
If Intersect(rDataVal, Target) Is Nothing Then Exit Sub
Dim res As Variant
  res = Application.Match(Target.Value, Range("MyEntries"), 0)
 
  If IsError(res) Then
       Application.CutCopyMode = False
       MsgBox "This Cell holds a Data Validation List to help you choose"
  End If
End Sub

 

[SP321]

Not sure to totally understand ...

However, you can test following macro

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rDataVal As Range
Set rDataVal = Range("B7:B600")
If Intersect(rDataVal, Target) Is Nothing Then Exit Sub
Dim res As Variant
  res = Application.Match(Target.Value, Range("MyEntries"), 0)
 
  If IsError(res) Then
       Application.CutCopyMode = False
       MsgBox "This Cell holds a Data Validation List to help you choose"
  End If
End Sub

I tried, it creates the drop down menu, but it can be still overwritten with copy paste and no message pops up

Well to explain it a bit, users copy the data from somewhere else and paste it into my file, sometimes they need to change some details, thats why the drop down menu.
What was sometimes happening though that the data from their table was a bit different (typos mostly), or they pasted it into the wrong column, then my power bi reports weren't accurate, I would like to prevent that

 

[James006]

Sorry but Copy Paste, at my end is rejected ...

Can you explain how do you perform a Paste in the restricted area .... with a macro ? or manually ?

 

[SP321]

Sorry but Copy Paste, at my end is rejected ...

Can you explain how do you perform a Paste in the restricted area .... with a macro ? or manually ?

normally Ctr+C, Ctrl+V
when debugging it marks yellow this part of the code: res = Application.Match(Target.Value, Range("MyEntries"), 0)
(I used the correct name in "MyEntries" from name manager)

 

[James006]

Sorry ... but cannot replicate the error you have ... with my test, at my end ...

 

[SP321]

Sorry ... but cannot replicate the error you have ... with my test, at my end ...

Nevermind, I'll keep trying to find out, thank you for your help