Checkbox reset - prevent loop

[PaulZak]

I have a checkbox on form. When user checks or unchecks it (changes it), I provide a msg prompt yes/no asking if user wants to proceed. If the user selects NO, I want to revert the checkbox back to original value (checked or unchecked).

For example: User checks box that was unchecked, user selects not to proceed when prompted, checkbox returns to unchecked value.

Problem is I get a single loop when code returns the box to original value (unchecked) and the message prompt appears again.
1) [BeforeUpdate] event: I am grabbing the original value and storing in a variable.
2) [Click] event: I'm running Yes/No prompt and code the resets the box to original value (chkgox.value = x). (I've also tried placing it in [AfterUpdate] event, instead but not any better.)

The only way I can see to solve this is to create/use a "runcode" variable (yes/no flag) to prevent the [Click] event's code from running the second time (after checkbox value is set back to original value by code).

Maybe I'm missing some obvious other approach. Is there a simpler way (i.e., using different events)???

 

[sous2817]

Can you post the code you have?

If you're using a worksheet event, you should include something along the lines of:

Application.EnableEvents = False

at the start and

Application.EnableEvents = True

at the end.

 

[PaulZak]

Thanks for the quick reply. Checkbox and code exist in a UserForm. I did try your idea (which sounded familiar) but it didn't work. See following. Any other ideas???

If MsgBox("Your changed the checkbox" & vbCr & vbCr & _
"Do you want to proceed?", vbYesNo, "CHANGE USER TYPE?") = vbNo Then
Application.EnableEvents = False 'Tried per suggestion
Me.CkBox_RunStdUser.Value = gbOriginalUserTypeCkBoxValue
Application.EnableEvents = True
Exit Sub
End If

 

[sous2817]

Enabling or disabling events won't work on a userform. With the exit sub statement, I don't see why it would loop.

 

[PaulZak]

I've stepped through code. It loops as soon as the code runs that changes checkbox back to original value (because user does not want to proceed). So I guess it's as if the user unchecked the box and so VBA sees it as another click. I agree this seems odd; there's an update or change but no actual "click" involved.

So, back to my goal. Isn't this a reasonable design standard: User clicks checkbox, app asks if user meant to do that, user response to Yes/No msg with no ("Opps, my bad") and the app returns box back to original value/state? (Otherwise, user clicks no and subsequent code doesn't run, which is correct, but box stays checked which is confusing. Also, this doesn't allow user to check box later, if they are ready at that time.)

 

[Norie]

What exactly are you trying to do?

Perhaps there is another approach.

eg not doing the check using the checkbox's own events

 

[ChrisM]

Along Norie's line, this is kind of a sticky practice. If you think about programs you use daily, how many times do you get warned when you click a checkbox? It's kind of intrusive.

But, to answer your question, I assume you're using the Click event? Why not tie your Yes/no question to the Mouse Down event? Head the problem off before the change even gets made.

 

[Norie]

I think Chris has a good idea there


I was trying to think of another checkbox event you could use but came up blank.

 

[PaulZak]

Brilliant, ChrisM! Using this event works as desired.

I don't use this event much so it wasn't on my radar.

Thanks, all.