Set button target location to move with button and cell location when inserting or deleting new columns and rows

Chipperzs

New Member
Joined
Aug 11, 2011
Messages
7
I have an equation that calculates elapsed time. You click on a button and it inputs the current time in the cell below it. Then in the cell to the right of the input value is a cell with the equation that calculates the remaining time.

The Macro for the Button is:

Sub Sub_Timestamp1()
Range("I4:I4").Select
Range("I4:I4").Value = Time()
End Sub

The function for the cell is:
=IF(I4="","",(NOW()-ROUNDDOWN(NOW(),0))-I4)

I would like to be able to Delete or Add Columns and Rows left and above the location where the Button and the cell that it puts the time into and have both update without having to change the VBA code to the new range location every time.

I've used the ActiveCell.Value command before but I don't want to force the user to click on the cell below the button every time. I've played with the idea of adding a unique set of letters (or a code) in the cell directly under the Button and one row above the cell that the button inputs the time into. Then... (some how) use the match() function to find the range of the cell containing the code and basing the Button input location on that range.

I'd also like to use a Command Button (ActiveX Control) rather than a Button (Form Control) and associate it with a macro.

Any help would be great. Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top