Button with moving cell reference

[Cat129]

Hi All,

I'm not sure how to do this so hoping you can help

I have a button on an excel form and when you click it, it send an automated email to someone to say they approve the document.

What I would also like is for it to put the date they clicked the button and their user name into certain cells. I know how to do the code for this, the issue I have is the button and related cells are at the bottom of the form.

If a user adds or removes rows above the sign off boxes at the bottom in the form the cell reference for the date and name move either up or down. Is there a way to dynamically ensure the date and name always go into the cells adjacent to the button?

The bottom of the sheet looks like the below

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]Signature[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Designer[/TD]
[TD]Person 1[/TD]
[TD]Approval Button[/TD]
[TD]Signature[/TD]
[TD]username generated by code[/TD]
[TD]Date[/TD]
[TD]date generated by code[/TD]
[/TR]
[TR]
[TD]Chief Engineer[/TD]
[TD]Person 2[/TD]
[TD]Approval Button[/TD]
[TD]Signature[/TD]
[TD]username generated by code[/TD]
[TD]Date[/TD]
[TD]date generated by code[/TD]
[/TR]
[TR]
[TD]Gate Keeper[/TD]
[TD]Person 3[/TD]
[TD]Approval Button[/TD]
[TD]Signature[/TD]
[TD]username generated by code[/TD]
[TD]Date[/TD]
[TD]date generated by code[/TD]
[/TR]
[TR]
[TD]Program Manager[/TD]
[TD]Person 4[/TD]
[TD]Approval Button[/TD]
[TD]Signature[/TD]
[TD]username generated by code[/TD]
[TD]Date[/TD]
[TD]date generated by code[/TD]
[/TR]
[TR]
[TD]Data Coordinator[/TD]
[TD]Person 5[/TD]
[TD]Approval Button[/TD]
[TD]Signature[/TD]
[TD]username generated by code[/TD]
[TD]Date[/TD]
[TD]date generated by code[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Cat

 

[Cat129]

So tell me do you have anything in column D starting at row 55? Or is the cell empty.

And if you were to double click on D55 you want Username in J55 and Todays date in N55 is that correct?

D55 is a blank cell currently, when the form is filled in, it will have the name of a person in there (The Design Engineer). For example Joe Bloggs. This will be the case for D55:D59 as per the screenshot above. But this cell reference could change as explained earlier.

Once the form is approved by all, I then need the person to click / double click something that will send an approval email, add the user name of the person that did the clicking into cell J55 and add the date it was clicked into N55

Each person will need to do this process who is listed in the screen shot above. The names are not the same for each form.

Hopefully this screenshot helps a little more

<a href="https://ibb.co/hzT6Xz"><img src="https://preview.ibb.co/gaaWze/Capture.jpg" alt="Capture" border="0"></a><br /><a target='_blank' href='https://poetandpoem.com/Louis-Esson/poems'>poems by Louis Esson</a><br />

 

[Cat129]

I just did some googling on how to create an action from a double click, and I'm pretty sure I can make that work.

I can unmerge some cells and create a cell with Approve in it, that when double clicked would do what I need. I assume I can then just write the code in the form RC so that when rows are added or removed it wouldn't affect the code.

Thank You so much, just didn't know this possible

 

[Fluff]

How do i reference rw in the vba to ensure it inputs into the correct cell. Would it be something like Range.("J" & rw). ?

Yes, that absolutely right

 

[My Aswer Is This]

So looks like you do not need any more help from me.
Take care glad to see you know how to do this.

 

[Cat129]

So looks like you do not need any more help from me.
Take care glad to see you know how to do this.

You have been really really helpful, and taught me something I didn't know.

Thank you

 

[My Aswer Is This]

You have been really really helpful, and taught me something I didn't know.

Thank you

When you have your script doing what you want show us the script.
​