In article <
8f59e925-d332-499d-ba1a-dab9d48536dcn@googlegroups.com>,
jared.schmid@visitbicycleworld.com says...
If I'm using the following code how would I modify it so that the date is only inserted when a specific word is put into a cell?
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 1;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,3];
// Sheet you are working on
var SHEETNAME = 'Sheet1'
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//checks that we're on the correct sheet.
if( sheet.getSheetName() == SHEETNAME ) {
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
}
}
Interesting puzzle. Rather than focus on the code you have here, I prefer to stand back and work from the desired goal, which I presume is to keep a record of the last change date of any cell - if the latest value matches some string.
Most of my VBA has been done in other applications than Excel, so no guarantees!
Firstly, I see Events operate at the Worksheet level, rather than per cell or range. So I'd put my code into the event handler for the "worksheet.change" Event:
The standard event handler, always called "Worksheet_Change", returns a value "Target", which is the range (one or more cells) whose change triggered the Event.
These web pages explain how to do that:
https://www.thesmallman.com/vba-worksheet-change-events (nice video)
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.change https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells- change
https://www.extendoffice.com/documents/excel/3884-excel-monitor-cell- changes.html
http://www.cpearson.com/excel/Events.aspx
You'd then look to see if the latest value of the cell or cells matches your string of interest (lots of functions to play with to do that) and then take whatever action you want. Personally, I'd want to consider commenting the cell itself rather than writing to another cell - see this:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.addcomment
I note your code relies on the ActiveCell: I'm not confident that this would always be the changed cell. If you start editing a cell, and then click elsewhere (or tab to next cell) your changes will be retained but the ActiveCell would surely be the one you just entered? I can't find anything online to say either way!
Hope that helps.
--
Phil, London
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)