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]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,893
Members
431,772
Latest member
dannyboi1

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
Top