PDA

View Full Version : Show the time and date of when the code is being executed



cavemonkey
06-13-2007, 11:55 PM
Hi

I have this spreadsheet that has a code to calculate some figures. So in order to execute the code, the user have to press this particular button found on the worksheet for the code to start.

Is there anyway to show the date and time the user press this button to execute the code? And also to prevent any repeating update in a single day is there anyway to prevent the user from updating after the code has been updated for the day?

I don't really have any examples to show due to restrictions placed on the excel file that I'm working on. Sorry.

Charlize
06-14-2007, 12:29 AM
Something like this ? http://vbaexpress.com/forum/showpost.php?p=85803&postcount=6

mdmackillop
06-14-2007, 01:09 AM
Sub Timing()
With Sheets(1).Range("IV1")
If .Value = "" Then
.Value = Now
Else
If Now - .Value < 1 Then
MsgBox "Too soon"
Else
MsgBox "OK"
End If
End If
End With
End Sub

mikerickson
06-14-2007, 01:18 AM
Add this to the beginning of the click event.

Sub Button1_Click()
With ActiveSheet
.Names.Add Name:="lastUpdate", RefersTo:=Date
.Names("lastupdate").Visible = False
.Shapes("Button 1").Visible = False
.Range("a1").Value = Now
End With
Rem code for updating
End Sub
and put this in the codemodule for the Sheet.

Private Sub Worksheet_Activate()
If Evaluate(ActiveSheet.Names("lastUpdate").RefersTo) = CDbl(Date) Then
ActiveSheet.Shapes("Button 1").Visible = False
Else
ActiveSheet.Shapes("Button 1").Visible = True
End If
End Subsets a maximum of one update per day. And when that update is performed, the date and time will be put in A1.

This code is for a Forms command Button. The syntax will be slightly different if you are using ToolBox controls.

mikerickson
06-14-2007, 01:48 AM
Here's a different variation on that theme, instead of making the button invisible, it changes the caption and disables the button until tomorrow.
Sub Button1_Click()
With ActiveSheet
.Names.Add Name:="lastUpdate", RefersTo:=Date
With .Shapes("Button 1")
.OLEFormat.Object.Caption = "Cannot update" _
& vbCrLf & "until " & Format(Date + 1, "ddd m/d")
.OLEFormat.Object.AutoSize = True
.ControlFormat.Enabled = False
End With
End With

Rem code for updating
End Sub

Private Sub Worksheet_Activate()
With ActiveSheet.Shapes("Button 1")
If Evaluate(ActiveSheet.Names("lastUpdate").RefersTo) = CDbl(Date) Then
.OLEFormat.Object.Caption = "Cannot update" _
& vbCrLf & "until " & Format(Date + 1, "ddd m/d")
.OLEFormat.Object.AutoSize = True
.ControlFormat.Enabled = False
Else
.OLEFormat.Object.Caption = "Press to update"
.OLEFormat.Object.AutoSize = True
.ControlFormat.Enabled = True
End If
End Sub