1004 error

zendog1960

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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top