Using Named Ranges in Worksheet_Change

[mmateos]

Hi all,

This is my first time posting a thread but a few years following yours.
I have the following code which I can't make it work. It works when its refer to a single cell but it does't works when I use a named range. I have a named range "TGCEne" in the active worksheets with data validation from a named range "CodeExp" from another worksheet. Could you please let me know what I am doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim strInput As String
      
   'assumed name range = "CodeExp" from worksheet "Sheets1" where data to validate list are.
   
   If Target.Address <> Range("CodeExp") Then Exit Sub
   If Target.Value = "" Then Exit Sub
    
   'find the hyphen and extract the required text
   strInput = Trim(Mid(Target.Value, 1, Application.Find("-", Target.Value) - 1))
   
   'disable events before writing to worksheet
   On Error Resume Next
   Application.EnableEvents = False
      Target.Value = strInput
   Application.EnableEvents = True
   


End Sub

I would appreciate your help.
Many thanks from Madrid

 

[baitmaster]

welcome to the board

Looks to me like the issue is one of visibility of code to other code

Code in a standard module can see all of the workbook quite easily, but can't for example see private code within a worksheet module

Code in a worksheet module can see standard code modules easily, but can't so easily see other areas of the workbook. In your case, I think it is struggling because it is looking in it's worksheet, but not seeing the named range which is an object within a different worksheet

I think one of the following should work:
- Either refer to the named range including it's worksheet, i.e. [worksheets("sheets1").Range("CodeExp")]
- Or split the bulk of your code and paste it separately into a standard code module, so you'd have the worksheet change event in your worksheet module, calling the separate code in a standard module

Private Sub Worksheet_Change(ByVal Target As Range)
WSChange Target
End Sub
Sub WSChange(Target As Range)
Dim strInput As String
'assumed name range = "CodeExp" from worksheet "Sheets1" where data to validate list are.
If Target.Address <> Range("CodeExp") Then Exit Sub
If Target.value = "" Then Exit Sub
 
'find the hyphen and extract the required text
strInput = Trim(Mid(Target.value, 1, Application.Find("-", Target.value) - 1))
'disable events before writing to worksheet
On Error Resume Next
Application.EnableEvents = False
   Target.value = strInput
Application.EnableEvents = True
End Sub