Worksheet_Change(ByVal Target As Range) Not Working

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
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:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
EnableEvents has probably been set to False and not reset to True.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,057
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
1612753031314.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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.
 

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
Office Version
  1. 2011
Platform
  1. MacOS
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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
Top