1004 error

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
459
Office Version
  1. 2019
Platform
  1. Windows
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:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
Thanks Leith. I also figured out that since I was locking the sheets I forgot to check the allow cell formatting. duh...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top