1004 error

[zendog1960]

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

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.

 

[Leith Ross]

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...

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]

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

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]

Thanks Leith. I also figured out that since I was locking the sheets I forgot to check the allow cell formatting. duh...

 

[Leith Ross]

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.