PDA

View Full Version : Application.EnableEvents



V.B.A.02
07-25-2017, 08:40 AM
Hello, I have been just stuck in below code, please help,
it seems there is something wrong as enableevents doent seem to work, and also msgbox shows twice.


Private Sub ComboBox1_Change()
If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Application.EnableEvents = False
ComboBox1.Value = ""
MsgBox "Incorrect Input.", vbCritical, "Input Error !": Exit Sub
End If


End Sub

Regards,

Paul_Hossler
07-25-2017, 08:51 AM
1. You can / should use the [#] icon to surround your code in [ CODE ] tags to make it easier to read

2. Not tested, but .EnableEvents is a global setting, and if you turn it off, you most likely need to turn it back on



Private Sub ComboBox1_Change()
If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Application.EnableEvents = False
ComboBox1.Value = ""
Application.EnableEvents = True '<<<<<<<<<<<<<<<<<<<<<<
MsgBox "Incorrect Input.", vbCritical, "Input Error !"
Exit Sub
End If

V.B.A.02
07-25-2017, 08:57 AM
Sure Paul, will use [#], didnt know earlier. Thanks.
I have tried what you suggested but didnt work that..

mdmackillop
07-25-2017, 09:07 AM
I use this structure to ensure events are reset in the event of an error in the body of the code

On Error GoTo Exits
Application.EnableEvents = False

'your code

Exits:
Application.EnableEvents = True

YasserKhalil
07-25-2017, 09:08 AM
Hello
Please put the code between code tags ..
As for events it is important to enable them again ... Like that


Private Sub ComboBox1_Change()
If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Application.EnableEvents = False
ComboBox1.Value = ""
Application.EnableEvents = True

MsgBox "Incorrect Input.", vbCritical, "Input Error !": Exit Sub
End If
End Sub

YasserKhalil
07-25-2017, 09:09 AM
It seems that I came late to the party. I didn't see any of those replies
I am sorry ..

V.B.A.02
07-25-2017, 09:13 AM
Hello Yaseer, I always use Application.EnableEvents = True, however in this code it repeats the line again and msgbox is shown twice. which I dont want.



Private Sub ComboBox1_Change() If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Application.EnableEvents = False
ComboBox1.Value = ""
Application.EnableEvents = True

MsgBox "Incorrect Input.", vbCritical, "Input Error !": Exit Sub
End If

I am not sure if it does not work because of ComboBox which I am using.

And sure I will use # to put codes. thanks

V.B.A.02
07-25-2017, 09:18 AM
Well, I found solution for this issue, please follow below link, in case if there is need.:thumb

http://www.cpearson.com/excel/SuppressChangeInForms.htm

Thanks All for Help..

mdmackillop
07-25-2017, 09:21 AM
Try BeforeUpdate

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Cancel = True
ComboBox1.Value = ""
MsgBox "Incorrect Input.", vbCritical, "Input Error !"
End If
End Sub

V.B.A.02
07-26-2017, 08:15 AM
Hello mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop),

I am using code you suggested see below, it works great, thanks.

However there is a small problem.
Once this code is executed, cursor goes to the next textbox on userform and blinks there, I want user to be able to input values in same textbox, I have put setfocus but its not working. Please help.





Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
With UserForm1.TextBox1
CheckNumers = TextBox1.Value
OnlyNumbers 'Check Only Numbers
TextBox1.Value = CheckNumers
End With
UserForm1.TextBox1.SetFocus
Cancel = False
End Sub





Thanks

mdmackillop
07-26-2017, 10:44 AM
Remove ​ Cancel = False

V.B.A.02
07-27-2017, 02:04 AM
Removing Cancel = False, causes other page on userform not to select.
Next events dont fire

mdmackillop
07-27-2017, 02:51 AM
Can you post your workbook?

V.B.A.02
07-27-2017, 03:43 AM
Hello mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop),

I could not share the original workbook, I have created and attached a workbook to show you the issue I am facing.

I want user to input numbers only in Sample Text Box and "Y" or "N" in Sample Combo Box.

If I do not use 'Cancel = False', it gets stuck. and If I use 'Cancel = False' then courser moves to next object.

Hope this sample workbook explains the issue I am facing.

Help is appreciated, Thanks

mdmackillop
07-27-2017, 04:16 AM
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With UserForm1.TextBox1
If Not IsNumeric(.Value) Then
.Value = vbNullString
Cancel = True
End If
End With
End Sub

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ComboBox1.Value = "Y" And Not ComboBox1.Value = "N" Then
Cancel = True
ComboBox1.Value = ""
MsgBox "Incorrect Input.", vbCritical, "Input Error !"
End If
End Sub

p45cal
07-27-2017, 04:33 AM
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Not OnlyNumbers(.Value) Then
MsgBox "Only are numbers allowed.", vbCritical
.Value = vbNullString
Cancel = True
End If
End With
End Sub

Function OnlyNumbers(WhatImChecking)
OnlyNumbers = IsNumeric(WhatImChecking) Or WhatImChecking = vbNullString
End Function
and lose the:
Public CheckNumers As String

V.B.A.02
07-27-2017, 04:37 AM
with this code:

If a user by mistake enters characters in Textbox1 then user would have to input number in TextBox1, user cant do anything else like, going to other page filing info there first and later on coming back to this page and enter number or leave this page blank.

I need user to have flexibility like,

If user wants to fill Textbox1 first and while filling it user enters char (incorrect input) and then user wishes to leave Textbox1 blank and wishes to go to page2 etc.


Let me if thats' possible, or else I will have to live with that. then may be other alternates like instructions to users.

Well Thanks a lot.

mdmackillop
07-27-2017, 04:48 AM
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim chk
With UserForm1.TextBox1
If Not IsNumeric(.Value) Then
.Value = vbNullString
chk = MsgBox("Don't you know what a number is?" & vbCr & vbCr & "Go back and fix this now?", vbQuestion + vbYesNo)
If chk = vbYes Then Cancel = True
End If
End With
End Sub

p45cal
07-27-2017, 04:52 AM
I need user to have flexibilityThen don't do ANY checking while the user is entering data into the various fields (unless you want simply to have a warning(s) appear), and do all your checking of all the fields that you want to check when the user presses the OK button (or whatever) when the data that has been input into the usrform is about to be used elsewhere.

V.B.A.02
07-27-2017, 05:03 AM
:thumb

mdmackillop
07-27-2017, 05:08 AM
Then don't do ANY checking while the user is entering data
I agree with this method. If your users can't get it right the first time, they should be made to trawl through the form again, fixing whatever they got wrong!