macro to date stamp to the right of the macro command button

[Roha]

I would like to make a macro tied to a command button to stamp the current
date/time in the cell directly to the right of the button. So if I put the
button in cell A1, I would want the date stamp to be in B1. I also want to
be able to copy this button down a sheet and have the macro advance
accordingly. I have the macro working find, and I tried to use R1C1
referencing, but the button cell is not necessarily the active cell, so it
stamps the date/time in the wrong adjacent cell.
Any ideas?

 

[Dave Peterson]

If you're copying the button down the worksheet and it's working, I bet you're
using the button from the Forms toolbar (not the commandbutton from the control
toolbox toolbar).

And this means that you have a macro assigned to this button.

You could use something like this:

with activesheet.buttons(application.caller).topleftcell.offset(0,1)
.numberformat = "mmmm dd, yyyy hh:mm:ss"
.value = now
end with

If the button covers more than one cell (horizontally), then you may want to
offset by more than 1 column.

 

[Roha]

Thanks Dave. That worked!!

If you're copying the button down the worksheet and it's working, I bet you're
using the button from the Forms toolbar (not the commandbutton from the control
toolbox toolbar).

And this means that you have a macro assigned to this button.

You could use something like this:

with activesheet.buttons(application.caller).topleftcell.offset(0,1)
.numberformat = "mmmm dd, yyyy hh:mm:ss"
.value = now
end with

If the button covers more than one cell (horizontally), then you may want to
offset by more than 1 column.

 

[Jay]

I have tried, but this isnt working for me. I am using a command button in
Cell A5...I would like to be able to click on that button and have it put the
current date in B5. What would I have to put in the Microsoft Visual Basic?

 

[Dave Peterson]

If you're using a commandbutton, then maybe:

Option Explicit
Private Sub CommandButton1_Click()
With Me.CommandButton1.TopLeftCell.Offset(0, 1)
.NumberFormat = "mmmm dd, yyyy hh:mm:ss"
.Value = Now
End With
End Sub