Results 1 to 5 of 5

Thread: 1004 error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular zendog1960's Avatar
    Join Date
    Sep 2003
    Location
    Camas, Washington
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 1004 error

    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 by zendog1960; Sep 23rd, 2019 at 03:24 PM.

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,850
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1004 error

    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
    Sincerely,
    Leith Ross

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,253
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: 1004 error

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular zendog1960's Avatar
    Join Date
    Sep 2003
    Location
    Camas, Washington
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1004 error

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

  5. #5
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,850
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1004 error

    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.
    Sincerely,
    Leith Ross

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •