Excel Enquiry

  Dellman 16:15 26 Sep 2007

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 2007

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 2007


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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires