Prevent a command button from being clicked twice
 

I am using excel 2003 to keep lap times in a dirt bike race. I have a command
button that is clicked to record the time the race starts. The code is below.

Private Sub StartButton_Click()
Worksheets("Timing Sheet").Range("A6").Value = Format(Now, "h:mm:ss")
End Sub

What I need to do is to ensure that the button is not clicked accidently
after the race has started. Is there a way that I can count how many times it
has been clicked and if it is more than once it comes up with a warning along
the lines of "the race has started are you sure you want to change the race
start time" with a yes/no option.

The other thing I would like to do is store a backup copy of the original
start time somewhere else in the worksheet in a way that it is nopt updated
every time the button is clicked.

Any help will be gladly appreciated.

Thank you

Prevent a command button from being clicked twice
 

Two approaches (and who knows how many more) would work.

One is to declare a global variable in a standard module (i.e., NOT in
the sheet or ThisWorkbook code modules - you'll have to insert one, if
you don't already have one) - something like

Public gbStartedRace As Boolean

Then, modify your _Click event code to:

Private Sub StartButton_Click()

Dim lResponse As Long

If gbStartedRace = True Then
lResponse = MsgBox("Did you really want to restart the race?",
vbYesNo)
If lResponse = 6 Then
'This is the value if the 'Yes' button was clicked
Worksheets("Sheet1").Range("g2").Value = Format(Now,
"h:mm:ss")
Exit Sub
ElseIf lResponse = 7 Then
Exit Sub
End If
End If

Worksheets("Sheet1").Range("g2").Value = Format(Now, "h:mm:ss")
gbStartedRace = True

End Sub
[/highlight]

(I played with your cells and sheet names - change them back)

This works because a global variable (one declared as Public outside a
sub or function in a standard code modue) keeps its value even if
execution has passed out of the subroutine that set the value. Also,
unspecified Booleans are always False until they are given a value.

Another way to do this would be to add another button. In the
StartButton_Click event, have the code disable the StartButton

Worksheets("Sheet1").StartButton.Enabled = False

and set up the ResetButton's ResetButton_Click event to enable the
StartButton

Worksheets("Sheet1").StartButton.Enabled = True

The code in StartButton_Click would be simpler - and you wouldn't need
the code module or the gbStartedRace variable...

Good luck! :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110599

Prevent a command button from being clicked twice
 

James

Works a treat. Thank you very much for your time and effort.


"jamescox" wrote:


Two approaches (and who knows how many more) would work.

One is to declare a global variable in a standard module (i.e., NOT in
the sheet or ThisWorkbook code modules - you'll have to insert one, if
you don't already have one) - something like

Public gbStartedRace As Boolean

Then, modify your _Click event code to:

Private Sub StartButton_Click()

Dim lResponse As Long

If gbStartedRace = True Then
lResponse = MsgBox("Did you really want to restart the race?",
vbYesNo)
If lResponse = 6 Then
'This is the value if the 'Yes' button was clicked
Worksheets("Sheet1").Range("g2").Value = Format(Now,
"h:mm:ss")
Exit Sub
ElseIf lResponse = 7 Then
Exit Sub
End If
End If

Worksheets("Sheet1").Range("g2").Value = Format(Now, "h:mm:ss")
gbStartedRace = True

End Sub
[/highlight]

(I played with your cells and sheet names - change them back)

This works because a global variable (one declared as Public outside a
sub or function in a standard code modue) keeps its value even if
execution has passed out of the subroutine that set the value. Also,
unspecified Booleans are always False until they are given a value.

Another way to do this would be to add another button. In the
StartButton_Click event, have the code disable the StartButton

Worksheets("Sheet1").StartButton.Enabled = False

and set up the ResetButton's ResetButton_Click event to enable the
StartButton

Worksheets("Sheet1").StartButton.Enabled = True

The code in StartButton_Click would be simpler - and you wouldn't need
the code module or the gbStartedRace variable...

Good luck! :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=110599