Date and time stamp, control button

[Guest]

I have a speardsheet I use to track sales calls. As of now it only tracks
totals by means of a control button that acts as a counter. (upon clicking
the button, it adds "1" to a cell for a total.) What I would like to do is
add to the control button so that each click is recorded seperately and date
and time stamped, preferably on a seperate sheet. any suggestions? (I'm not
the most educated on Visual Basics, I would be more than happy to e-mail what
I have now if that would be easier.)

 

[Bob Phillips]

Private Sub CommandButton1_Click()
Const SHEET_NAME As String = "mylog" '<=== change to suit
Dim sh As Worksheet
Dim this As Worksheet
Dim iNext As Long

Set this = ActiveSheet
On Error Resume Next
Set sh = Worksheets(SHEET_NAME)
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = SHEET_NAME
Set sh = Worksheets(SHEET_NAME)
iNext = 1
Else
iNext = sh.Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
sh.Cells(iNext, "A").Value = Now
sh.Cells(iNext, "A").NumberFormat = "dd mmm yyyy hh:mm:ss"
this.Activate

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

I put a commandbutton from the control toolbox on a worksheet.

I added a worksheet called LogSheet to the workbook. I put headers in row 1.

Then I used this code under the commandbutton:

Option Explicit
Private Sub CommandButton1_Click()
Dim DestCell As Range
With Worksheets("LogSheet")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With DestCell
.Value = Application.UserName
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

End Sub

I wasn't sure what else you want to keep track of, though. I just used the
username (from tools|Options|General tab) and the date/time.

 

[Guest]

Thanks Bob that looks great, however I am afraid it will take me hours to
figure out what goes where. Hope you don't mind dumbing it down a bit for me.
Here is the infomation I have:
for the ticker:

Private Sub DIALS_Click()
Range("b5") = Range("b5") + 1 "sheet name is ticker"
End Sub

I want the stamp to go to sheet name "stamp"
I have 24 buttons so I would want each stamp to go in a cooresponding row or
column. I can duplicate it for all the buttons, but I didn't know if that was
relevant or not.
If it's not to much to ask could you send this back with the appropriate
info in the "macro?, I don't even know what they are called."
Also would I be able to add the new stuff to the original button? or do I
need two? Please forgive my inexperience. Thanks

 

[Bob Phillips]

For the DIALS button

Private Sub DIALS_Click()
Const SHEET_NAME As String = "stamp"
Const THIS_COL As String = "A" '<=== change per button
Dim sh As Worksheet
Dim this As Worksheet
Dim iNext As Long

Set this = ActiveSheet
On Error Resume Next
Set sh = Worksheets(SHEET_NAME)
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = SHEET_NAME
Set sh = Worksheets(SHEET_NAME)
iNext = 1
Else
iNext = sh.Cells(Rows.Count, THIS_COL ).End(xlUp).Row + 1
End If
sh.Cells(iNext, THIS_COL ).Value = Now
sh.Cells(iNext, THIS_COL ).NumberFormat = "dd mmm yyyy hh:mm:ss"
this.Activate

End Sub

Change THIS_COL to a different column for each button

What do you mean by adding new stuff?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Dave, I will give that a try as well. Basically what I am trying to do
it track all my stats. I do sales calls and I want to be more effective by
tracking my calls, when I talk to people, when I leave a message, when I
email. Then I want to be able to see what time, day of week I am most
effective.
Eventually I want to be able to narrow things down to age and demographics
of clients, again to maximize efficiency, but I think I will wait until I can
have a programmer write that one for me.