Worksheet Change Events based on different values? Excel 2010

Eean

New Member
Joined
May 26, 2010
Messages
44
Hi Everyone,

Hope you can help with this one.

Is it possible to have a Worksheet Change event based on different values in the Target?
The Code I have is:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
  ActiveSheet.Unprotect

If Target.Cells.Value <> "Out Of Service" Then Exit Sub
       
           If Not Intersect(Target, Range("N4:N100")) Is Nothing Then
            With Target(1, 18)
            .Value = Date & " " & Time
             End With
           End If
           
                                 
            Target.Offset(0, 1).Select
                   
 If Target.Cells.Value <> "Back In Service" Then Exit Sub
       
           If Not Intersect(Target, Range("N4:N100")) Is Nothing Then
            With Target(1, 19)
            .Value = Date & " " & Time
             End With
             End If
           
                 
       Target.Offset(0, 1).Select
                
                   
End Sub



Or is there a different way I should be tackling this?

Thanks in advance!
 
Hi,

I think this is an outline of what you want:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorHandler
    
    'was a cell in N4:N100 changed?
    If Not Intersect(Target, Range("N4:N100")) Is Nothing Then
        
        'was only 1 cell changed?
        If Target.CountLarge = 1 Then
            
            'must disable events if we are going to change a cell's value
            'because otherwise this event handler will get called again!
            Application.EnableEvents = False
            
            Me.Unprotect
            
            Select Case LCase$(CStr(Target.Value))
                
                Case "out of service"
                    Target(1, 18).Value = Date & " " & Time
                
                Case "back in service"
                    Target(1, 19).Value = Date & " " & Time
                
                Case Else
                    'what happens here?
                    
            End Select
        
        
        End If
    End If
ErrorExit:
    
    On Error Resume Next
    
    'ensure worksheet is protected and EnableEvents is set back to true
    'even if an error occured
    Me.Protect
    Application.EnableEvents = True
    
    Exit Sub
ErrorHandler:
    
    Debug.Print "Error: " & Err.Number & vbNewLine & "Desc: " & Err.Description
    
    Resume ErrorExit
End Sub
You have to disable events otherwise the changing a cell's value within your code will cause the event handler to get called again. The offshoot of this is it's just as important to ensure you re-enable events at the end of the code, even if an error occured - which means you need error handling.

I've re-used your code where possible but you could make improvements - for example you could use Format() and Now() to get the datetime stamp rather than concatenating Date() and Time().
 
Last edited:
Upvote 0
That's brilliant Colin!
Thank you so much. I've changed my code to NOW () instead of the concatination as you suggested.

The Column N:N is a dropdown list the user can choose from. There are 4 different values that they can choose. But I only want the Date/Timestamp if they have selected "Out Of Service" or "Back In Service".
If they accidentally choose "Out Of Service" or "Back In Service" and choose one of the other 2 options I need the timestamp to be erased.

Can I use 'Case Else' to do this or would I need to write a new code for each of the options?
 
Upvote 0
Hi Colin! Thanks for helping me learn rather than just give me the answer!! VERY much appreciated! :)

This is what I added.

Code:
  Select Case LCase$(CStr(Target.Value))
                
                Case "out of service"
                    Target(1, 18).Value = Now
                
                Case "back in service"
                    Target(1, 19).Value = Now
               
                Case Else
                 Target(1, 18).ClearContents
                 Target(1, 19).ClearContents

Thanks!!!
 
Upvote 0
Learning is what it's all about. Well done for making those changes yourself! :)
 
Upvote 0

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