History of Dates to be traced in Excel (2010) in DD/MM/YYYY format

Gajendran Yadhav

Board Regular
Joined
Sep 8, 2023
Messages
51
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hi all,

I'm working on a file where in I am tracking a series of incidents that occur on certain dates.

For this I have written a macro. This works. but only issue I'm facing is, this automatically stores the data in MM/DD/YYYY format that too only for dates [1 - 12] / MM / YYYY.
for dates [13 - 31] / MM / YYYY, it takes the actual input as it is.

This happens only at the first entry. On subsequent entries, this issue is not arising. It stores the date in correct format. (refer pic below)

I have data validation in the input cell (column M) for the users to enter the date in DD/MM/YYYY format only.

My code is here..
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    Dim dateList As String
    Dim existingList As String
    Dim lastRow As Long
    Dim btn As Button

    ' Find the last row in column M with data
    lastRow = Me.Cells(Me.Rows.Count, "M").End(xlUp).Row
  
    ' Check if the changed cell is within column M and from M5 onwards
    If Not Intersect(Target, Me.Range("M5:M" & lastRow)) Is Nothing Then
        ' Iterate through each changed cell in column M
        For Each cell In Intersect(Target, Me.Range("M5:M" & lastRow))
            ' Check if the cell in column M has a date value
            If IsDate(cell.Value) Then
                ' Retrieve existing date list from corresponding row in column G
                existingList = Me.Cells(cell.Row, "G").Value
              
                ' If the existing list is not empty, append a comma and space
                If existingList <> "" Then
                    existingList = existingList & ", "
                End If
              
                ' Append the new date to the existing date list
                existingList = existingList & Format(cell.Value, "dd/mm/yyyy")
              
                ' Write the updated date list back to the corresponding row in column G
                Me.Cells(cell.Row, "G").Value = existingList
            End If
        Next cell
    End If

Please help to resolve this...

Pictorial representation:
1710748313025.png

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 2:
I have the Command Button in column U (ref pic).
is it possible to protect that particular row (A:T & V, W) alone on clicking the button.

Note that column X1 : last is already protected and hidden. worksheet protected.
Column G is also protected as it obtains the date from column M.
User will be able to input data only in cells B to T. (Column A is protected - value is default population of S.No.s based on entry in subsequent column B)
Column U is button.
V & W - asks question on clicking the button and stores the answer in to V & W.


Any help is much appreciated.
Thanks in advance.
 
Hi @Alex Blakenburg & @MARK858

It's great that your code worked. Thanks a lot.

PS: Your code is looping (calling itself), suggest you Turn Off/On EnableEvents
Application.EnableEvent =FALSE at the start and Application.EnableEvent =TRUE at the end
And btw, I have incorporated the snippet as well. Thanks for the suggestion as well.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,226,531
Messages
6,191,596
Members
453,667
Latest member
JoeH7745

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