User Form with CheckBoxes
 

Can someone advise why this doesn't work; the userform (AskReClearData) shows
up okay, but when I check the 'CheckBoxClearCS' within the form (done in a
frame via the Control Toolbox) and then hit ok, the code essentially skips
the 5th line (re clear contents). If the checkbox is NOT selected and I hit
OK, then the code runs correctly.

Private Sub OKButton_Click()
Application.EnableEvents = True
If CheckBoxClearCS.Value = True Then
Worksheets("MMS for CS").Activate
Range("O3").ClearContents
Else
MsgBox ("Not checked")
Worksheets("MMS for CS").Activate
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

User Form with CheckBoxes
 

Application.EnableEvents can not be used for UserForms or for controls in a
Worksheet.
However, with or without events - when I test it, your code works quite well
anyway.


What if you try the same thing this way...

Private Sub OKButton_Click()
Dim wb As Workbook, cs As Worksheet
Set wb = ThisWorkbook
Set cs = wb.Sheets("MMS for CS")
cs.Activate
If Me.CheckBoxClearCS.Value = True Then
cs.Cells(3, 15).Delete 'Same as Range("O3")
ElseIf Me.CheckBoxClearCS.Value = False Then
MsgBox "Not checked", vbInformation, "MSS for CS"
cs.Cells(1, 1).Select
End If
Set cs = Nothing
Set wb = Nothing
Unload Me
End Sub






"Paige" schreef in bericht
...
Can someone advise why this doesn't work; the userform (AskReClearData)
shows
up okay, but when I check the 'CheckBoxClearCS' within the form (done in a
frame via the Control Toolbox) and then hit ok, the code essentially skips
the 5th line (re clear contents). If the checkbox is NOT selected and I
hit
OK, then the code runs correctly.

Private Sub OKButton_Click()
Application.EnableEvents = True
If CheckBoxClearCS.Value = True Then
Worksheets("MMS for CS").Activate
Range("O3").ClearContents
Else
MsgBox ("Not checked")
Worksheets("MMS for CS").Activate
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

User Form with CheckBoxes
 

..enableevents won't stop the userform events (or events for controls on a
worksheet) from firing--but it can be used in that kind of code to stop events
from firing when the code does something to that sheet.

Although, I would have guessed that
application.enableevents = false
would have been used at the top and then "= true" at the bottom of the code.


moon wrote:

Application.EnableEvents can not be used for UserForms or for controls in a
Worksheet.
However, with or without events - when I test it, your code works quite well
anyway.

What if you try the same thing this way...

Private Sub OKButton_Click()
Dim wb As Workbook, cs As Worksheet
Set wb = ThisWorkbook
Set cs = wb.Sheets("MMS for CS")
cs.Activate
If Me.CheckBoxClearCS.Value = True Then
cs.Cells(3, 15).Delete 'Same as Range("O3")
ElseIf Me.CheckBoxClearCS.Value = False Then
MsgBox "Not checked", vbInformation, "MSS for CS"
cs.Cells(1, 1).Select
End If
Set cs = Nothing
Set wb = Nothing
Unload Me
End Sub

"Paige" schreef in bericht
...
Can someone advise why this doesn't work; the userform (AskReClearData)
shows
up okay, but when I check the 'CheckBoxClearCS' within the form (done in a
frame via the Control Toolbox) and then hit ok, the code essentially skips
the 5th line (re clear contents). If the checkbox is NOT selected and I
hit
OK, then the code runs correctly.

Private Sub OKButton_Click()
Application.EnableEvents = True
If CheckBoxClearCS.Value = True Then
Worksheets("MMS for CS").Activate
Range("O3").ClearContents
Else
MsgBox ("Not checked")
Worksheets("MMS for CS").Activate
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

--

Dave Peterson

User Form with CheckBoxes
 

You sure you're looking at the correct sheet?

And if you do have an event that fires when you make a change, maybe you want
something like:

Private Sub OKButton_Click()

If CheckBoxClearCS.Value = True Then
'why select
Application.EnableEvents = False
Worksheets("MMS for CS").Range("O3").ClearContents
Application.EnableEvents = true
Else
MsgBox "Not checked"
Worksheets("MMS for CS").select
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

Paige wrote:

Can someone advise why this doesn't work; the userform (AskReClearData) shows
up okay, but when I check the 'CheckBoxClearCS' within the form (done in a
frame via the Control Toolbox) and then hit ok, the code essentially skips
the 5th line (re clear contents). If the checkbox is NOT selected and I hit
OK, then the code runs correctly.

Private Sub OKButton_Click()
Application.EnableEvents = True
If CheckBoxClearCS.Value = True Then
Worksheets("MMS for CS").Activate
Range("O3").ClearContents
Else
MsgBox ("Not checked")
Worksheets("MMS for CS").Activate
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

--

Dave Peterson

User Form with CheckBoxes
 

Thank you both; got it working! Really appreciate the assistance.

"Dave Peterson" wrote:

You sure you're looking at the correct sheet?

And if you do have an event that fires when you make a change, maybe you want
something like:

Private Sub OKButton_Click()

If CheckBoxClearCS.Value = True Then
'why select
Application.EnableEvents = False
Worksheets("MMS for CS").Range("O3").ClearContents
Application.EnableEvents = true
Else
MsgBox "Not checked"
Worksheets("MMS for CS").select
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

Paige wrote:

Can someone advise why this doesn't work; the userform (AskReClearData) shows
up okay, but when I check the 'CheckBoxClearCS' within the form (done in a
frame via the Control Toolbox) and then hit ok, the code essentially skips
the 5th line (re clear contents). If the checkbox is NOT selected and I hit
OK, then the code runs correctly.

Private Sub OKButton_Click()
Application.EnableEvents = True
If CheckBoxClearCS.Value = True Then
Worksheets("MMS for CS").Activate
Range("O3").ClearContents
Else
MsgBox ("Not checked")
Worksheets("MMS for CS").Activate
Range("A1").Select
End If
CheckBoxClearCS.Value = 0
AskReClearData.Hide
End Sub

--

Dave Peterson