Excel Enquiry

  Dellman 16:15 26 Sep 07

I have a Spread sheet that is accessed by multiple users on a LAN where they add new rows of data. Within the worksheet, I have a column that needs to reflect the "current" month in format "MMM-YY" in which that data for that month was input. I want to make this field foolproof so that that any "new" input into new cells in that column can only ever display the month that the data was input. However, historic dates in the column to correctly report the old information. I'd want that cell "locked" so that the format could not be amended by other users.

Any ideas/solutions?

  VoG II 16:31 26 Sep 07

This will add the date in column B if there is an input in column A (column number 1). Right click the sheet tab > View Code. Copy and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then Target.Offset(0, 1).Value = Format(Date, "mmm-yyyy")
End Sub

then close the code window and enter something in column A.

To change the column that triggers the date entry change the 1 in this statement to the column number

If Target.Column = 1

To change the number of columns to the right that receives the date, change the 1 here

Target.Offset(0, 1).Value

  Dellman 16:45 26 Sep 07


This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac