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

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?

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

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).topleftcel l.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 wrote:

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

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

Thanks Dave. That worked!!

"Dave Peterson" wrote:

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).topleftcel l.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 wrote:

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

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

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" wrote:

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).topleftcel l.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 wrote:

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

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

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



Jay wrote:

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" wrote:

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).topleftcel l.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 wrote:

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


--

Dave Peterson