How to utilize a check box

[mikeyboy]

Hi, I'm trying to learn how I can utilize a check box to switch a macro on or off. Currently I use an "If" statement to do so. What I'd like to do is use this example to learn how (in beginner terms, please) how to construct and code a check box to do what this "If" statement does?

Thank you very much.

Sub Deletecountdown()
' Macro recorded 2/12/2002
If Sheets("Options").Range("H14") = "Off" Then Exit Sub
PauseTime = 120 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start
ClearTROPaste
End

End Sub

[ This Message was edited by: mikeyboy on 2002-05-15 17:17 ]

[ This Message was edited by: mikeyboy on 2002-05-15 17:17 ]

[Juan Pablo González]

I don't understand...

You kind of have it in there...

If CheckBox1.Value = True then
'Execute macro
else
'Do not
End If

or

If Not CheckBox1 then Exit Sub

What do you mean by "construct the checkbox" ?

[mikeyboy]

So I would write the code like this and that's all there is to it? I'll give it a shot.



Sub Deletecountdown()

If Not CheckBox1 then Exit Sub

PauseTime = 120 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start
ClearTROPaste
End

End Sub

[Tom Schreiner]

After seeing both replies, I think I may have missed what you're looking for here?
I already wrote the code if it'll help..
This will start a counter when the checkbox is checked and stop the counter when it is unchecked.
Make sure the variables are declared in the General decalrations area of you module(the very top)

Option Explicit

Dim TotalTime As String

Dim StartingTime As Date

Dim EndingTime As Date



Private Sub CheckBox1_Click()

Range("A1") = ""

If Not CheckBox1 Then GoTo DoneLoop

StartingTime = Now

Do While CheckBox1

DoEvents ' Yield to other processes.

Loop

DoneLoop:

EndingTime = Now

TotalTime = Format(EndingTime - StartingTime, "HH:MM:SS")

Range("A1") = TotalTime

ClearTROPaste

End

End Sub

Tom

A note about your Timer...
If you run your code, as is, right before midnight, the Timer will be reset to 0 and you will end up in a 24 hour loop....
Place this line in your loop:
If Timer = 0 then Start = 0

[ This Message was edited by: TsTom on 2002-05-15 17:47 ]

[mikeyboy]

Thanks for the replies, What I'm looking to do is learn how I can put a checkbox in a sheet to stop a macro, in this case "Deletecountdown" if the check mark is removed.

The code I tried using the checkbox code didn't work. It End Sub with or without the check. I see code is now entered in the sheet I put the check on and perhaps the problem is I'm not linked to that sheet as the "Deletecountdown is installed in a module? I'm trying to understand this so simple code is appreciated.

Thanks

[James]

Hi mikeyboy,

maybe a togglebutton would be a better option?

James

[mikeyboy]

Thanks James, Tomorow I can play around some more. I'm trying to imitate how windows programs use checkbox's as a means users can elect preferences.

Thanks

[NateO]

Mike, don't know where the issue is but you can try:

CheckBox1.Value = xlOn 'Box has been checked



and



CheckBox1.Value = xlOff 'Box hasn't been checked  



or



CheckBox1.Value <> xlOn 'Box hasn't been checked  

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 19:00 ]

[Juan Pablo González]

Checkboxes have two (well, three), but two values: TRUE and FALSE. That's it.

If you insert a checkbox using the Control Toolbar, then, you could use the code like I told you.

If you insert it using the Forms Toolbar, then that's a little different, but, main thing is that they return either TRUE or FALSE.

[NateO]

I'm thinking of the dialog sheet method, which is a little older....Probably not the way you did this, probably ought to disregard.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 19:20 ]