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:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,878
Office Version
2010, 2007
Platform
Windows
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
35,590
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,878
Office Version
2010, 2007
Platform
Windows
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,089,421
Messages
5,408,137
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top