Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet_Change Cleanup (2003)

    With this code, columns C, D, J, K, and L update really slowly. However, column G is just fine. Any help to make this more efficient would be much appreciated. Thanks!!

    Option Explicit
    Const mcstrDefaultType As String = "New Policy"
    Const mcstrDefault As String = "N"

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Change case in Column C or D to Proper
    If Not Intersect(Target, Range("C")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    Call ChangeCases1
    ActiveSheet.Protect
    Application.EnableEvents = True
    End If

    'Change case in Column J, K, or L to Upper
    If Not Intersect(Target, Range("J:L")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    Call ChangeCases2
    ActiveSheet.Protect
    Application.EnableEvents = True
    End If

    'Enter Default Values for Data Entry
    Dim rngCell As Range, rngCheck As Range
    If Intersect(Target, Me.Columns(6)) Is Nothing Then Exit Sub
    On Error GoTo err_handler
    Application.EnableEvents = False
    Set rngCheck = Intersect(Target, Me.Columns(6))
    For Each rngCell In rngCheck
    If Len(rngCell.Offset(0, 1)) = 0 Then rngCell.Offset(0, 1) = mcstrDefaultType
    If Len(rngCell.Offset(0, 4)) = 0 Then rngCell.Offset(0, 4) = mcstrDefault
    If Len(rngCell.Offset(0, 5)) = 0 Then rngCell.Offset(0, 5) = mcstrDefault
    If Len(rngCell.Offset(0, 6)) = 0 Then rngCell.Offset(0, 6) = mcstrDefault
    Next rngCell

    leave:
    Application.EnableEvents = True
    Exit Sub

    err_handler:
    MsgBox "Error: " & Err.Description
    Resume leave

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet_Change Cleanup (2003)

    Is this a follow-up to <post:=635,729>post 635,729</post:>? If so, it would have been better to post it as a reply there. You haven't acknowledged Rory's reply, by the way.

    Since we don't know what ChangeCases1 and ChangeCases2 do, it is impossible to give advice.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet_Change Cleanup (2003)

    Yes it was. My apologies. I responded to Rory's reply and posted the code to ChangeCases1 and ChangeCases2.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet_Change Cleanup (2003)

    Reposted as <post:=635,855>post 635,855</post:>. This thread is locked to avoid duplication. Please post replies in the other thread.

Posting Permissions

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