Worksheet_Change(ByVal Target As Range) Not Working

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
I am trying to have the date and time entered into cells in columns "A", "B" and "C" with a "Y" placed in columns "C" or "E". Date only in column "A" and the time in columns "B" and "C". I have worked on this all day and cannot get it to work. I get no error messages but absolutly nothing happens. Below is my VBA code. Thanks in advance for any help I may receive.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim lastcell
lastcell = ActiveCell.Address

Set ws = ActiveWorkbook.Worksheets("Sheet1")
With ws

.Unprotect

Application.EnableEvents = False
Application.ScreenUpdating = False

If Not Intersect(Target, Range("C6:C36,E6:E36")) Is Nothing Then
    If .Cells(Target.Row, "C") = "N" Then
        .Cells(Target.Row, "A") = ""
        .Cells(Target.Row, "B") = ""
    End If

    Else
    If .Cells(Target.Row, "C") = "Y" Then
        If .Cells(Target.Row, "A") = "" Then
            .Cells(Target.Row, "A") = Now()
            .Cells(Target.Row, "B") = Now()
        Else
            .Cells(Target.Row, "A") = ""
            .Cells(Target.Row, "B") = ""
        End If

        If .Cells(Target.Row, "E") = "N" Then
            .Cells(Target.Row, "D") = ""
        End If
        Else
        If .Cells(Target.Row, "E") = "Y" Then
            If .Cells(Target.Row, "D") = "" Then
                .Cells(Target.Row, "D") = Now()
            End If
            Else
                .Cells(Target.Row, "D") = ""
            End If
    End If
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

ws.Range(lastcell).Select

End Sub
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
EnableEvents has probably been set to False and not reset to True.
 
Upvote 0
Put in a breakpoint and see if it fires.
If so, put this in the Immdiates window and press enter, then try again
VBA Code:
Application.EnableEvents = True
 
Upvote 0
1612753031314.png
 
Upvote 0
@anwaee2
When posting vba code, please use code tags to keep the code formatted, making it much easier for helper to read, de-bug or copy. My signature block below has more help on that.
On this occasion I have fixed the post for you.
 
Upvote 0
Got it working. Thanks again to all. I am sorry about the code, I had it indented but it must have went away when I copy/pasted it. And I have tried dozens of time to use the xl2bb code but could not get it to work on my mac. Maybe there is a new update and I can try again. Thanks again.
 
Upvote 0
I had it indented but it must have went away when I copy/pasted it.
It does - unless you use code tags. Follow this link to see how to use them: How to Post Your VBA Code

And I have tried dozens of time to use the xl2bb code but could not get it to work on my mac.
It does not work with Excel 2011 for MacOS. see version information near the top of this page: XL2BB - Excel Range to BBCode
In any case XL2BB is not for posting code, it is for posting parts of worksheets.

Glad you got the code working though. (y)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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