Run time error 1004 - application defined error

[SHEETMAGIC]

Hi there,
I have had to reinstall my whole XP system recently.
It's taking me some time to get excel back into the state I had it in before reinstalling... It was set up to do all the various things I like and need. If only I could recall exactly everything I had done to get there !!

Anyway...
I am getting an error on code that used to run just fine. The code has not changed... only Excel has changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nn As String, sht As Worksheet

   If Not Intersect(Target, Range("H9:J10")) Is Nothing Then
      nn = ActiveSheet.Range("R6")
      ActiveSheet.Name = "Correct the week date"
         If SheetExists(nn) Then
            MsgBox ("This date already exists. Please pick another end date")
         Else
            ActiveSheet.Name = nn
         End If
   End If
End Sub

This line generates the error: ActiveSheet.Name = nn
The error I get is:
"Run time error: '1004'
Application-defined or object-defined error"

Please let me know what simple change I need to make to excel to get this to work as it use to. Your help is greatly appreciated.

I am using Excel 2003 SP3.

With my thanks and regards,


Jonathan[/code]

 

[Datsmart]

I get that error if R6 is empty.
What value do you have in cell R6?

 

[SHEETMAGIC]

Thanks Datsmart.

Actually I get the error no matter what.
If I select that cell then I get this error.

What used to happen is that when I put a date into that cell (or any value for that matter) it would then rename the sheet to that value. If the cell was empty it would not generate an error (as it always starts out empty until I click on it and put in a value).

I am sure there is some Excel component or something-a-rather that I had installed in the past and need to reinstall.

Any ideas?

Thanks...

Jonathan

 

[SHEETMAGIC]

I guess the primary question here is:
Is there any particular Excel "component" (or whatever one might call it) that has to be installed in order for Excel to impliment the command "Activesheet.name"

I notice that it has actually managed to rename the sheet "Correct the week date" which is what it is meant to do when that cell is empty. However, whenever I put focus on or off that cell I get the error as specified (even when I have put a date in there).

Jonathan