Solved Stop The Automatic Update Of A Date Column

November 8, 2017 at 04:26:05
Specs: Windows 10
please help
I have formula called =IF(ISBLANK(T964),"",TODAY()) in Column S

and i would like VBA code to update with the date and freeze, don't update everyday. below VBA code i have
but seems to be not working. please help.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 19 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -1).Value = Date
Application.EnableEvents = True
End Sub


Dim astrLinks As Variant
astrLinks = wbNew.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
On Error Resume Next
With wbNew.Sheets(1)
.Select
.Unprotect
.BreakLink Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks
On Error GoTo 0
.Protect
End With


See More: Stop The Automatic Update Of A Date Column

Reply ↓  Report •

#1
November 9, 2017 at 06:40:12
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

Second, I'm a little confused by your requirements.

First, your question seems to be about locking in the date that the Worksheet_Change code puts in a cell. but you also posted some cell code related to breaking links. Is that section of code somehow related to your date question?

Second, I'm not sure I understand your process, which makes it hard to offer an answer to your question.

1 - You said: "I have formula called =IF(ISBLANK(T964),"",TODAY()) in Column S"

The formula seems to be monitoring a single cell (T964). Was that just an example or is the formula in multiple Column S cells, monitoring multiple Column T cells? Please clarify.

2 - Your Worksheet_Change code is looking for changes in Column 19, which is Column S. Are you aware that a change in a formula result does not trigger a Worksheet_Change? The Worksheet_Change event is not triggered by a recalculation of a cell. You would need to use the Calculate event to capture the calculation.

3 - Finally, assuming that you are trying to monitor the change in Column S, the code is going to put a date in Column R. It is not clear to me which date you are trying to freeze. Is it the date produced by the Column S formula or the date that the code would put in Column R if the code was actually working?

The bottom line is that you need to explain your process as well as your requirements with some more clarity.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
November 10, 2017 at 02:37:21
✔ Best Answer
Hi DerbyDad03

first of all thank you for your response. My concern originally was that my VB code wasn't working with my spreadsheet as of ICT issue. Its fixed now and code is working fine as how i wanted in the worksheet.

thanks very much for trying to help, I will keep those information you mentioned above in mind for future.


Reply ↓  Report •
Related Solutions


Ask Question