CODEWITHSUNDEEP
excelvba
kitarika
kitarika

Reputation: 175

How to click on a cell to activate macro for adjacent cell

I'm trying to put together an Excel-based quick workflow management tool where my staff can click on a particular cell and the cell adjacent to it "clocks" in the current date.

The table looks something like: Name | Start Date | Completion Date | Click

The Start Date will automatically populate based on cell value change under Name (which I've figured how how to make this happen), I want the Completion Date to then populate the current date against the relevant line that the staff has "clicked on" via hyperlink under Click as work may not be completed in chronological order.

Is this a possibility that macro would be able to do without needing the staff to manually change the value of a particular cell in order to "clock on" the completion date?

Any advice would be greatly appreciated!

Upvotes: 0

Views: 65

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

In a regular module:

Function SetDate() As Range
    'set the date one cell to the left
    Application.Caller.Offset(0, -1).Value = Now
    Set SetDate = Selection '<< need to return a range
End Function

Hyperlink formula:

=HYPERLINK("#SetDate()","<< Click to set date")

Upvotes: 2

Related Questions