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]

Anyone able to help?

 

[Cat129]

Anyone have any ideas how to do this? Really struggling myself

 

[My Aswer Is This]

You said:

I have a button on an excel form ​

Do you mean a Command button on a UserForm?

You said:

If a user adds or removes rows above the sign off boxes at the bottom in the form​

What are sign off boxes?
Cells cannot be on a UserForm.

I think I need more clear information.

 

[Cat129]

You said:

I have a button on an excel form ​

Do you mean a Command button on a UserForm?

You said:

If a user adds or removes rows above the sign off boxes at the bottom in the form​

What are sign off boxes?
Cells cannot be on a UserForm.

I think I need more clear information.

Sorry, its a regular excel document that we use as a sign off form.

I have added in active x controllers which are the green buttons in the image and assigned a macro to them that sends an approval email. The active x isn't inside the cell as such just hovering over. I wasn't sure how to show that in my example

Currently the top approval box is in row 55. I only know how to write the username and date code when I set it to a specific cell, once it moves because someone has added or removed a row above the code stops working or inputs the information in the wrong place on the document.

Thank you so much for you help with this

 

[Fluff]

This will show you what row the button is on

Private Sub CommandButton1_Click()
Dim rw As Long
   rw = Me.CommandButton1.TopLeftCell.Row
   MsgBox rw
End Sub

But it takes in position from the cell behind the top left corner of the button

 

[My Aswer Is This]

Having a activex button for every row on a sheet and a script to perform a task on cells to it's right could be a difficult task requiring many buttons and scripts

Why not have a user double click a cell which then performs a task on adjacent cells.
This would require only one script and no buttons.

Would something like that work for you?
Show me the script you have in your buttons.

 

[Cat129]

This will show you what row the button is on

Private Sub CommandButton1_Click()
Dim rw As Long
   rw = Me.CommandButton1.TopLeftCell.Row
   MsgBox rw
End Sub

But it takes in position from the cell behind the top left corner of the button

Brilliant, I have to be honest I'm not the best at writing in VBA, how would I do the next step? Reference the cell I want the information to be input into.

The first command button would show D55, and I need the username to appear in J55 and the date in N55. How do i reference rw in the vba to ensure it inputs into the correct cell. Would it be something like Range.("J" & rw). ?

Thanks for your help

 

[Cat129]

Why not have a user double click a cell which then performs a task on adjacent cells.
This would require only one script and no buttons.

I didn't know you could do this, I assumed I had to use an active x controller as a button. How would I do that?

Thank You

 

[My Aswer Is This]

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?