Gajendran Yadhav
Board Regular
- Joined
- Sep 8, 2023
- Messages
- 51
- Office Version
- 2010
- 2007
- Platform
- 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..
Please help to resolve this...
Pictorial representation:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.
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:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.