Using Named Ranges in Worksheet_Change

mmateos

New Member
Joined
Feb 5, 2013
Messages
1
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?

Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top