Run time error 1004 - application defined error

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
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.
Code:
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]
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I get that error if R6 is empty.
What value do you have in cell R6?
 

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
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

Board Regular
Joined
May 3, 2005
Messages
209
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,508
Members
412,599
Latest member
Schu94
Top