Push button to put dade in adjacent column- and pin to a cell so it is copied with a row

buckyswider

New Member
Joined
Feb 25, 2009
Messages
18
Hi all, another question where I've spent a couple hours on research but still fell a little short of my goal...

I'm doing an inventory spreadsheet for fire gear. One row per piece of equipment for different things like serial number, size, etc.

I would like to add a pushbutton control for inventory purposes. So say column C has a button control 'Inventory' or something like that. When that button is clicked/pushed (would be much easier to perform inventory on a tablet), it places the current date in the adjacent column "D". So at a glance we can tell the last time something was accounted for. Will also create a corresponding pair of columns for for a "Clean" date. (Goal would be to add conditional formatting to the date columns so we can easily tell when something has been accounted for or cleaned in a while; if it hasn't been cleaned in more than a year the cell turns red, etc.).

I also want to be able to make this a cell property- so when we add a new piece of equipment we just need to copy an existing row.

The only way I can figure out how to do it now is with an Active X control. And I can only get the date to appear in the "last clicked" cell, not a fixed point (i.e. the adjacent cell), and it also does not copy when I copy the row it is in.

is this doable? Thanks!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
So say column C has a button control 'Inventory' or something like that. When that button is clicked/pushed (would be much easier to perform inventory on a tablet), it places the current date in the adjacent column "D".

Maybe try this for your date stamp in column D. With the cursor in a cell in column D, down/up arrow to the row of the equipment desired, then do a Ctrl + ; keystroke.
That is, hold down the Ctrl key and top the semi colon (;) key. Presto! you have the current date in that cell.

I'm not understanding the "last clicked" and "fixed point" nor the "does not copy" capers.

Howard
 
Upvote 0
:)

Thanks Howard! My <eventual> goal is to be able to give someone a tablet to walk around the firehouse and do inventory, so I wanted a pushbutton so a keyboard would not be a necessity.

But I think I solved my problem. I was inserting a "Command Button (ActiveX Control)" and when I instead used "Button (Form Control)" it works as expected. As to my word soup (sorry about that): with the ActiveX control, when the button was clicked it would put the date in whatever cell you happen to have been in prior to clicking the button. So for example if the button was in B3, I wanted the date to appear in the adjacent cell C3. But instead: say I had just updated something in G20. And then when I clicked on the command button in B3, the current date would appear in G20, not C3. As far as "does not copy": When we add items to the inventory, I want to be able to ctrl-c an existing row and insert that as a new row. When the ActiveX control was on there, the copy/paste sequence would NOT contain the command button. So it would've had to be created from scratch every time.

BUT, with the Form Control instead of the ActiveX, it DOES copy.

And what I did was a pretty simple solution (provided to me by one of my buddies on a baseball message board! :) ): Just insert the form control in the proper cell, add a couple lines of code:

Rich (BB code):
Sub InsertDate()
    Dim x As Object
    Set x = ActiveSheet.Buttons(Application.Caller)
    x.TopLeftCell.Offset(0, 1).Value = Format(Now(), "mm/dd/yyyy")
End Sub

Thanks again!</eventual>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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