ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Time Stamp for specific macro.. (https://www.excelbanter.com/excel-programming/332750-time-stamp-specific-macro.html)

Murph
Time Stamp for specific macro..
 
I would like to have a time stamp inserted one column to the right of this
macro. The macro is inserted into an autoshape in Column P, so column Q would
contain the time stamp. The autoshape and macro are repeated in every row of
column P

I am not sure how to, upon clicking the autoshape, make the cell with the
autoshape the active cell and then tell the macro to insert the time stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Tom Ogilvy
Time Stamp for specific macro..
 
Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of this
macro. The macro is inserted into an autoshape in Column P, so column Q
would
contain the time stamp. The autoshape and macro are repeated in every row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with the
autoshape the active cell and then tell the macro to insert the time stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Murph
Time Stamp for specific macro..
 
i'm getting an error here

Set shp = ActiveSheet.Shapes(Application.Caller)
asking me to debug

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of this
macro. The macro is inserted into an autoshape in Column P, so column Q
would
contain the time stamp. The autoshape and macro are repeated in every row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with the
autoshape the active cell and then tell the macro to insert the time stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Tom Ogilvy
Time Stamp for specific macro..
 
application.Caller will only work if you do what you said:

"clicking the autoshape"

and it is used in the macro assigned to the autoshape.

If you are trying to run the macro manually, then there's your huckleberry.
(application.Caller is not defined)

--
Regards,
Tom Ogilvy



"Murph" wrote in message
...
i'm getting an error here

Set shp = ActiveSheet.Shapes(Application.Caller)
asking me to debug

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of
this
macro. The macro is inserted into an autoshape in Column P, so column
Q
would
contain the time stamp. The autoshape and macro are repeated in every
row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with
the
autoshape the active cell and then tell the macro to insert the time
stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Murph
Time Stamp for specific macro..
 
you were correct, I merely was trying to "run" the macro, after clicking the
autoshape it gave me an error on:

Set rng = shp.TopLeftCell.Offset(0, 1).Value

the autoshape holding the macro is in RowP, does that effect the
"TofLeftCell"?

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of this
macro. The macro is inserted into an autoshape in Column P, so column Q
would
contain the time stamp. The autoshape and macro are repeated in every row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with the
autoshape the active cell and then tell the macro to insert the time stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Murph
Time Stamp for specific macro..
 
you were correct, I merely was trying to "run" the macro, after clicking the
autoshape it gave me an error on:

Set rng = shp.TopLeftCell.Offset(0, 1).Value

the autoshape holding the macro is in RowP, does that effect the
"TofLeftCell"?


"Tom Ogilvy" wrote:

application.Caller will only work if you do what you said:

"clicking the autoshape"

and it is used in the macro assigned to the autoshape.

If you are trying to run the macro manually, then there's your huckleberry.
(application.Caller is not defined)

--
Regards,
Tom Ogilvy



"Murph" wrote in message
...
i'm getting an error here

Set shp = ActiveSheet.Shapes(Application.Caller)
asking me to debug

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of
this
macro. The macro is inserted into an autoshape in Column P, so column
Q
would
contain the time stamp. The autoshape and macro are repeated in every
row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with
the
autoshape the active cell and then tell the macro to insert the time
stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Tom Ogilvy
Time Stamp for specific macro..
 
No, that was my typo

set rng = shp.TopLeftCell.offset(0,1).Value

should be

set rng = shp.TopLeftCell.offset(0,1)

--
Regards,
Tom Ogilvy


"Murph" wrote in message
...
you were correct, I merely was trying to "run" the macro, after clicking
the
autoshape it gave me an error on:

Set rng = shp.TopLeftCell.Offset(0, 1).Value

the autoshape holding the macro is in RowP, does that effect the
"TofLeftCell"?


"Tom Ogilvy" wrote:

application.Caller will only work if you do what you said:

"clicking the autoshape"

and it is used in the macro assigned to the autoshape.

If you are trying to run the macro manually, then there's your
huckleberry.
(application.Caller is not defined)

--
Regards,
Tom Ogilvy



"Murph" wrote in message
...
i'm getting an error here

Set shp = ActiveSheet.Shapes(Application.Caller)
asking me to debug

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right
of
this
macro. The macro is inserted into an autoshape in Column P, so
column
Q
would
contain the time stamp. The autoshape and macro are repeated in
every
row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell
with
the
autoshape the active cell and then tell the macro to insert the
time
stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub

Tom Ogilvy
Time Stamp for specific macro..
 
No, my typo

Delete the .value on the end of the statement

--
Regards,
Tom Ogilvy


"Murph" wrote in message
...
you were correct, I merely was trying to "run" the macro, after clicking
the
autoshape it gave me an error on:

Set rng = shp.TopLeftCell.Offset(0, 1).Value

the autoshape holding the macro is in RowP, does that effect the
"TofLeftCell"?

"Tom Ogilvy" wrote:

Dim shp as Shape
Dim rng as Range
set shp = ActiveSheet.Shapes(Application.Caller)
set rng = shp.TopLeftCell.offset(0,1).Value
rng.Value = Now

--
Regards,
Tom Ogilvy

"Murph" wrote in message
...
I would like to have a time stamp inserted one column to the right of
this
macro. The macro is inserted into an autoshape in Column P, so column
Q
would
contain the time stamp. The autoshape and macro are repeated in every
row
of
column P

I am not sure how to, upon clicking the autoshape, make the cell with
the
autoshape the active cell and then tell the macro to insert the time
stamp
one cell to the right.This is the macro I am currently running.

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False

End Sub


All times are GMT +1. The time now is 05:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com