Worksheet_Change + Changing Named cell = Error 1004

RiTz21

New Member
Joined
Mar 3, 2009
Messages
9
Greetings,
I'm having an issue with the Worksheet_Change event.
When the user changes a specific value in the worksheet, I need to 'save' that new value to another worksheet unless it is already that same value.

t_SP1Class <- named cell in the current worksheet that I need to check

res_SpellListForSP <- named cell in another worksheet that HAS to hold the same value as t_SP1Class . If it already contains that value, then do nothing and exit.

Here is the code I am using, highlighting the
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Oups:
    If Application.Names("t_SP1Class").RefersToRange <> Application.Names("res_SpellListForSP").RefersToRange Then
        Application.Names("res_SpellListForSP").RefersToRange = Application.Names("t_SP1Class").RefersToRange
    End If

    Exit Sub

Oups:
    MsgBox Err.Description & " " & Err.Number
End Sub
This generates an "Application-defined or object-defined" error #1004 when I try to assign a new value to res_SpellListForSP.
I.E on the Application.Names("res_SpellListForSP").RefersToRange = Application.Names("t_SP1Class").RefersToRange line.

This problem is similar to this knowledge base article, yet that approach does not seem to solve my problem. How can I adapt to work around this?

Thank you for any assistance!!
RiTz21
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi and welcome to the board!!
If I understand you why not just
Code:
Private Sub WorkSheet_Change(ByVal Target as Range)
If Intesect(Target,Range("t_SP1Class")) Is Nothing Then Exit Sub
Range("res_SpellListForSP") = Target
End Sub

lenze
 
Upvote 0
Hi and welcome to the board!!
If I understand you why not just
Code:
Private Sub WorkSheet_Change(ByVal Target as Range)
If Intesect(Target,Range("t_SP1Class")) Is Nothing Then Exit Sub
Range("res_SpellListForSP") = Target
End Sub
lenze

I get an Error on Range("res_SpellListForSP") = Target
: Method 'Range' of object '_Worksheet' failed (1004)

Thank you for your help!!
RiTz21
 
Upvote 0
I located a similar problem as mine here, and I tried the solution proposed. Yet, that still generates the 1004 error..!! The worksheets are not protected (in case that could be an issue) - help!

Here is the code I tried:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Oups:
    Application.EnableEvents = False

    If Target.Address = "$E$3" Then
        ActiveWorkbook.Worksheets(26).Range("CC3").value = Target.value
    End If
    Application.EnableEvents = True

    Exit Sub

Oups:
    MsgBox Err.Description & " " & Err.Number
    Resume Next
End Sub
RiTz21
 
Upvote 0
*Addendum

I tried the code above in a new workbook (and simplified the sheet number to use sheets 1 and 2) and it worked fine!

What could be the difference between my workbook and a 'new' workbook so that it does not function in my Workbook and generates the 1004 error!? The worksheets are NOT protected... !??!

RiTz21
 
Upvote 0

Forum statistics

Threads
1,226,727
Messages
6,192,686
Members
453,746
Latest member
Bruce at Pubdarts

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