Timer for Workbook

[Akashwani]

Hi,

I have spent the last 3 hours searching/researching this and I have drawn a blank, so I'm back here for HELP.

I would like a "timer" to calculate, from the Workbook being opened to it being closed.

I would like a message to appear saying...
"You have been active for ??? minutes"

Is this a possibility?

Ak

 

[VoG]

Try this code in the ThisWorkbook module

Dim t As Date

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "You have been active for " & Format(Now - t, "hh:mm:ss")
End Sub

Private Sub Workbook_Open()
t = Now
End Sub

 

[Akashwani]

Hi VoG,

I get this message...

Compile Error:

Ambiguous name detected: Workbook_Open

This is what I have....

Private Sub Workbook_Open()
Dim s As String
MsgBox ("WELCOME")
s = InputBox("Enter your first name")
s = InputBox("Enter your password")
End Sub

Dim t As Date
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "You have been active for " & Format(Now - t, "hh:mm:ss")
End Sub
Private Sub Workbook_Open()
t = Now
End Sub

 

[canadapip]

Take the

t = Now

from the second Private Sub Workbook_Open(), and place it in the first right before end sub. Delete the second sub Workbook_Open. This should fix the issue.

As a recommendation to make the code easier to read, I'd suggest placing the declaration of "t" at the top of the module. your module would look like:

Dim t As Date

Private Sub Workbook_Open()
Dim s As String
MsgBox ("WELCOME")
s = InputBox("Enter your first name")
s = InputBox("Enter your password")
t = Now
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "You have been active for " & Format(Now - t, "hh:mm:ss")
End Sub

 

[Akashwani]

Thanks canadapip,

That is great and works a treat, however!!!

How would I get the message to say...

"You have been active for ?? seconds OR ?? minutes" instead of..
"You have been active for 00:00:49"

Thanks

Ak

 

[canadapip]

Hi Ak, try:

MsgBox "You have been active for " & Format(Now - t, "nn") & " minutes and " & Format (Now-t,"ss") & " seconds."

This will produce the message <i>You have been active for XX minutes and YY seconds.</i>

The "n" is how Excel' VB actually indicates the minutes place of a date/time if it isn't sandwiched in a completed hour format in order to eliminate confusion with month ("m"). It would be a good habit to get into using "n" instead of "m" for minutes.

If you wanted it to vary the message based on the number of seconds or minutes elapsed, you would need to build in an IF statement to check.

 

[Akashwani]

Yeah! Thanks canadapip

That does the job.

I don't know if I should start a new thread for this but...

I want to ask a question when the user opens the Workbook,
EG "What is the Capital of Canada?"
If the answer is correct then the workbook opens.
If the answer is incorrect, a message says "incorrect please try again"
How would I go about that?

Thanks

Ak

And thanks VoG for your prompt reply.

 

[canadapip]

Duplicate post

 

[canadapip]

You can easily do what you're looking for, and you're already implementing something similar with what you're doing assking for user name and password - all you need to do is compare the response provided to a set string.

For example:

sub TrialQuestions()
 Const strRightAnswer As String = "Ottawa"
 Dim strAnswer As String

 While Not (strRightAnswer = strAnswer)
  strAnswer = InputBox("What is the capital of Canada?")
  if Not (strRightAnswer = strAnswer) then MsgBox "Incorrect please try again."
 Wend
end sub

Now, as a warning, if the person never answers the question right, it will loop infinitely. The only way to break the loop is to hit Ctrl+Break (or Pause/Break) on some keyboards. At that point in time, all VB that is running in the spreadsheet will stop and you'll be flipped into the VBE.

Also, the comparison is case sensitive. On way to eliminated this would be to change the while loop to be:

 While Not (strRightAnswer = strAnswer)
  strAnswer = InputBox("What is the capital of Canada?")
  if Not (lcase(strRightAnswer) = lcase(strAnswer)) then MsgBox "Incorrect please try again."
 Wend

Canadapip

 

[Akashwani]

Hi canadapip,

Thanks for the reply.

The question did not appear!

The first 2 parts worked but not the new addition...

Dim t As Date
Private Sub Workbook_Open()
Dim s As String
MsgBox ("WELCOME")
s = InputBox("Enter your first name")
t = Now
End Sub
Sub TrialQuestions()
Const strRightAnswer As String = "Ottawa"
Dim strAnswer As String
While Not (strRightAnswer = strAnswer)
strAnswer = InputBox("What is the Capital of Canada?")
If Not (strRightAnswer = strAnswer) Then MsgBox "Iincorrect please try again."
Wend
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "You have been active for " & Format(Now - t, "nn") & " minutes and " & Format(Now - t, "ss") & " seconds."
Dim res
res = MsgBox(" Have you finished?", vbYesNo)
If res <> vbYes Then Cancel = True
End Sub

Have I inserted it in the correct place?

Ak