Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Time Stamp for specific macro..
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default 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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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








Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Time stamp Help DP7 Excel Worksheet Functions 2 August 31st 07 03:28 PM
Insert Time Stamp when macro is run Nagesh Excel Discussion (Misc queries) 3 March 13th 07 06:11 AM
Date/Time Stamp Macro Button Problem jp_m8 Excel Programming 5 August 26th 04 07:33 PM
Please help...I need a looping macro at a specific time Ed[_9_] Excel Programming 0 April 14th 04 05:24 PM
Saving a time stamp with a macro? Ray Morton Excel Programming 4 August 5th 03 12:24 AM


All times are GMT +1. The time now is 03:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement