software

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 ,quot; confirm vehicle 10quot; when
clicked on line 10 ??

thanksSub macro1g1()

If MsgBox(quot;Confirm Vehicle 1 On Sitequot;, vbOKCancel) = vbCancel Then Exit
Sub
Application.ScreenUpdating = False
Sheets(quot;slide 1quot;).Select
Range(quot;C4quot;).Select
ActiveCell.FormulaR1C1 = quot;=NOW()quot;
Range(quot;C4quot;).Select
Selection.NumberFormat = quot;h:mmquot;
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets(quot;Sheet2quot;).Select
End Sub

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.
Rich wrote:
gt;
gt; hiya, have a form which has approx 200 buttons on it to book things in and
gt; out, each button is linked seperate macros to insert the time, and the paste
gt; special it so that it dosnt change when the next save is hit
gt;
gt; is their a way to run one single macro, that will be able to tell the
gt; location of the pressed button (col \ row) and insert the time or do they
gt; need to be done individualy,
gt;
gt; it would also be handy if their was any way of getting the macro to read the
gt; vehicle name ,, so the message box would read ,quot; confirm vehicle 10quot; when
gt; clicked on line 10 ??
gt;
gt; thanks
gt;
gt; Sub macro1g1()
gt;
gt; If MsgBox(quot;Confirm Vehicle 1 On Sitequot;, vbOKCancel) = vbCancel Then Exit
gt; Sub
gt; Application.ScreenUpdating = False
gt; Sheets(quot;slide 1quot;).Select
gt; Range(quot;C4quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;=NOW()quot;
gt; Range(quot;C4quot;).Select
gt; Selection.NumberFormat = quot;h:mmquot;
gt; Selection.Copy
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Sheets(quot;Sheet2quot;).Select
gt; End Sub

--

Dave Peterson

sorry the first post wasnt very clear, the colums are in groups of 4, b= name
, c=quot;inquot; d=quot;outquot; , 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 quot;buttonsquot; are just words made in wordart, not genuine
buttons, and yes at the min they sit next to the name reliably

thanks

quot;Dave Petersonquot; wrote:

gt; These are buttons from the Forms toolbar that you placed on the worksheet???
gt;
gt; If yes and you placed those buttons very nicely--completely within the cell so
gt; you can rely on its position to determine which cell gets the time update--you
gt; can do it.
gt;
gt; But after rereading your post, it looks like the buttons are on a different
gt; sheet than where the time cell is.
gt;
gt; If that's true, then there has to be something to tie the each button to the
gt; cell that gets updated.
gt;
gt; Maybe you could use the button name:
gt;
gt; Btn_A1
gt; would change A1 of sheet2???
gt;
gt; or an adjacent column with the addresses of the cell that gets updated.
gt;
gt; It kind of depends on what you want to do next.
gt;
gt;
gt;
gt; Rich wrote:
gt; gt;
gt; gt; hiya, have a form which has approx 200 buttons on it to book things in and
gt; gt; out, each button is linked seperate macros to insert the time, and the paste
gt; gt; special it so that it dosnt change when the next save is hit
gt; gt;
gt; gt; is their a way to run one single macro, that will be able to tell the
gt; gt; location of the pressed button (col \ row) and insert the time or do they
gt; gt; need to be done individualy,
gt; gt;
gt; gt; it would also be handy if their was any way of getting the macro to read the
gt; gt; vehicle name ,, so the message box would read ,quot; confirm vehicle 10quot; when
gt; gt; clicked on line 10 ??
gt; gt;
gt; gt; thanks
gt; gt;
gt; gt; Sub macro1g1()
gt; gt;
gt; gt; If MsgBox(quot;Confirm Vehicle 1 On Sitequot;, vbOKCancel) = vbCancel Then Exit
gt; gt; Sub
gt; gt; Application.ScreenUpdating = False
gt; gt; Sheets(quot;slide 1quot;).Select
gt; gt; Range(quot;C4quot;).Select
gt; gt; ActiveCell.FormulaR1C1 = quot;=NOW()quot;
gt; gt; Range(quot;C4quot;).Select
gt; gt; Selection.NumberFormat = quot;h:mmquot;
gt; gt; Selection.Copy
gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; SkipBlanks _
gt; gt; :=False, Transpose:=False
gt; gt; Sheets(quot;Sheet2quot;).Select
gt; gt; End Sub
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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, quot;Cquot;)
If IsEmpty(myCell) Then
'keep it in column C
Else
Set myCell = .Cells(myShape.TopLeftCell.Row, quot;Dquot;)
End If

With myCell
.Value = Now
.NumberFormat = quot;mm/dd/yyyy hh:mm:ssquot;
End With
End With

End SubIt 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!)
ME @ Home wrote:
gt;
gt; sorry the first post wasnt very clear, the colums are in groups of 4, b= name
gt; , c=quot;inquot; d=quot;outquot; , when they click on in want the time to go in the c colum.
gt; and out the time goes in thed coloum,,,, it dosnt matter if the results are
gt; displayed on sheet 1 (underneath the buttons) or on page 2 , because page 2
gt; can always read from page 1 using the = function
gt;
gt; the in and out quot;buttonsquot; are just words made in wordart, not genuine
gt; buttons, and yes at the min they sit next to the name reliably
gt;
gt; thanks
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; These are buttons from the Forms toolbar that you placed on the worksheet???
gt; gt;
gt; gt; If yes and you placed those buttons very nicely--completely within the cell so
gt; gt; you can rely on its position to determine which cell gets the time update--you
gt; gt; can do it.
gt; gt;
gt; gt; But after rereading your post, it looks like the buttons are on a different
gt; gt; sheet than where the time cell is.
gt; gt;
gt; gt; If that's true, then there has to be something to tie the each button to the
gt; gt; cell that gets updated.
gt; gt;
gt; gt; Maybe you could use the button name:
gt; gt;
gt; gt; Btn_A1
gt; gt; would change A1 of sheet2???
gt; gt;
gt; gt; or an adjacent column with the addresses of the cell that gets updated.
gt; gt;
gt; gt; It kind of depends on what you want to do next.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Rich wrote:
gt; gt; gt;
gt; gt; gt; hiya, have a form which has approx 200 buttons on it to book things in and
gt; gt; gt; out, each button is linked seperate macros to insert the time, and the paste
gt; gt; gt; special it so that it dosnt change when the next save is hit
gt; gt; gt;
gt; gt; gt; is their a way to run one single macro, that will be able to tell the
gt; gt; gt; location of the pressed button (col \ row) and insert the time or do they
gt; gt; gt; need to be done individualy,
gt; gt; gt;
gt; gt; gt; it would also be handy if their was any way of getting the macro to read the
gt; gt; gt; vehicle name ,, so the message box would read ,quot; confirm vehicle 10quot; when
gt; gt; gt; clicked on line 10 ??
gt; gt; gt;
gt; gt; gt; thanks
gt; gt; gt;
gt; gt; gt; Sub macro1g1()
gt; gt; gt;
gt; gt; gt; If MsgBox(quot;Confirm Vehicle 1 On Sitequot;, vbOKCancel) = vbCancel Then Exit
gt; gt; gt; Sub
gt; gt; gt; Application.ScreenUpdating = False
gt; gt; gt; Sheets(quot;slide 1quot;).Select
gt; gt; gt; Range(quot;C4quot;).Select
gt; gt; gt; ActiveCell.FormulaR1C1 = quot;=NOW()quot;
gt; gt; gt; Range(quot;C4quot;).Select
gt; gt; gt; Selection.NumberFormat = quot;h:mmquot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; gt; gt; SkipBlanks _
gt; gt; gt; :=False, Transpose:=False
gt; gt; gt; Sheets(quot;Sheet2quot;).Select
gt; gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson