auto reading macro

[Guest]

hiya, have a form which has approx 200 buttons on it to book things in and
out, each button is linked seperate macros to insert the time, and the paste
special it so that it dosnt change when the next save is hit

is their a way to run one single macro, that will be able to tell the
location of the pressed button (col \ row) and insert the time or do they
need to be done individualy,

it would also be handy if their was any way of getting the macro to read the
vehicle name ,, so the message box would read ," confirm vehicle 10" when
clicked on line 10 ??

thanks


Sub macro1g1()

If MsgBox("Confirm Vehicle 1 On Site", vbOKCancel) = vbCancel Then Exit
Sub
Application.ScreenUpdating = False
Sheets("slide 1").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C4").Select
Selection.NumberFormat = "h:mm"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
End Sub

 

[Dave Peterson]

These are buttons from the Forms toolbar that you placed on the worksheet???

If yes and you placed those buttons very nicely--completely within the cell so
you can rely on its position to determine which cell gets the time update--you
can do it.

But after rereading your post, it looks like the buttons are on a different
sheet than where the time cell is.

If that's true, then there has to be something to tie the each button to the
cell that gets updated.

Maybe you could use the button name:

Btn_A1
would change A1 of sheet2???

or an adjacent column with the addresses of the cell that gets updated.

It kind of depends on what you want to do next.

 

[Guest]

sorry the first post wasnt very clear, the colums are in groups of 4, b= name
, c="in" d="out" , when they click on in want the time to go in the c colum.
and out the time goes in thed coloum,,,, it dosnt matter if the results are
displayed on sheet 1 (underneath the buttons) or on page 2 , because page 2
can always read from page 1 using the = function

the in and out "buttons" are just words made in wordart, not genuine
buttons, and yes at the min they sit next to the name reliably

thanks

 

[Dave Peterson]

Maybe you can assign each shape this macro:

Option Explicit
Sub testme()

Dim myShape As Shape
Dim myCell As Range

With ActiveSheet
Set myShape = .Shapes(Application.Caller)
Set myCell = .Cells(myShape.TopLeftCell.Row, "C")
If IsEmpty(myCell) Then
'keep it in column C
Else
Set myCell = .Cells(myShape.TopLeftCell.Row, "D")
End If

With myCell
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

End Sub


It uses the topleftcell that contains the shape to determine the row. If you
click on it and column C is empty, then column C gets the date/time. If column
C has something in it, then D gets the value (no matter if it already has
something in it or not!)