1004 error

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
449
Hello folks,

I am getting a "run time error 1004 application defined or object defined" error. I am adding the code below. I would appreciate if someone could look the code over and let me know if they can find out why I am getting this.

First things first: in the Range D2, I have that cell formatted for a date. Range I1 is just a name cell.

It worked for a while but I cannot figure our what has changed

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Target = Range("D2")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Format(Target, "mmm-dd-yy")
    GoTo EmpName
    ActiveWorkbook.Save
    Range("D2").Activate
    Exit Sub
Badname:
    MsgBox "Please revise the entry in D2." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)

EmpName:
    Set Target = Range("I1")
    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("I1")) Is Nothing Then
        Select Case Target.Value
            Case Is = ""
                Target.Value = "Enter Your Name Here"
                Target.Font.ColorIndex = 15
            Case Is = "Enter Your Name Here"
                Target.Font.ColorIndex = 15         <----- This is the line that is triggering the error
           Case Else
                Target.Font.ColorIndex = 1
        End Select
    End If
End Sub
If I comment out that error line, the error moves down to the Case Else line. It's like it isn't doing the select case statement correctly.
 
Last edited:

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,852
Hello zendog1960,

When using Worksheet_Change, or Worksheet_SelectionChange events, you must disable the events temporarily if you change the value of of cell or select a cell inside these event modules. Not doing this will cause erratic results and often times crash Excel. You must re-enable the events before you exit the event module at any point.

Here is code with these changes made to it...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Application.EnableEvents = False
    
    Set Target = Range("D2")
    If Target = "" Then GoTo Finished
    
    On Error GoTo Badname
        ActiveSheet.Name = Format(Target, "mmm-dd-yy")
        GoTo EmpName
        ActiveWorkbook.Save
        Range("D2").Activate
    GoTo Finished
    
Badname:
    MsgBox "Please revise the entry in D2." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)


EmpName:
    Set Target = Range("I1")
    If Target.Cells.Count > 1 Then GoTo Finished


    If Not Intersect(Target, Range("I1")) Is Nothing Then
        Select Case Target.Value
            Case Is = ""
                Target.Value = "Enter Your Name Here"
                Target.Font.ColorIndex = 15
            Case Is = "Enter Your Name Here"
                Target.Font.ColorIndex = 15         <----- This is the line that is triggering the error
           Case Else
                Target.Font.ColorIndex = 1
        End Select
    End If
    
Finished:
    Application.EnableEvents = True


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
You should never use keywords (such as Target) for the names of variables.
Also looking at your code I would think you'd be better off with a change event, rather than a selection change event.
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "D2" Then
      On Error GoTo Badname
      Me.Name = Format(Target, "mmm-dd-yy")
      On Error GoTo 0
   ElseIf Target.Address(0, 0) = "I1" Then
      Select Case Target.Value
         Case ""
            Application.EnableEvents = False
            Target.Value = "Enter Your Name Here"
            Target.Font.ColorIndex = 15
            Application.EnableEvents = True
         Case "Enter Your Name Here"
            Target.Font.ColorIndex = 15
         Case Else
            Target.Font.ColorIndex = 1
      End Select
   End If
Exit Sub
Badname:
    MsgBox "Please revise the entry in D2." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
End Sub
 

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
449
Thanks Leith. I also figured out that since I was locking the sheets I forgot to check the allow cell formatting. duh...
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,852
Hello zendog1960,

Good to know you have it under control. Fluff makes a good point about naming variables. It too is a common mistake that leads to hair pulling.
 

Forum statistics

Threads
1,077,827
Messages
5,336,611
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top