Disabling Events of a UserForm

[zakroma]

Hi everyone!

Could someone please explain how can I suppress events on user forms in Excel please?

I've got a workbook with 2 sheets.
My goal is to mark the checkbox on sheet1 that will hide some rows on sheet2.
Now the code for hiding works just fine, but it's the jumping from sheet1 to sheet2 is what I would like to suppress.

Now I've googled quite a bit and I've found these 2 links:
1) Suppressing Events In UserForms
2) http://www.mrexcel.com/forum/excel-...ng-events-initialize-subroutine-userform.html
3) excel vba userform enableevents - Stack Overflow

But I just can't figure out what do I need to add/remove from my code for it to work.

Here's the code:

[COLOR=#011993][FONT=Menlo]Public[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] EnableEvents [/FONT][/COLOR][COLOR=#011993][FONT=Menlo]As[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Boolean[/FONT][/COLOR][FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Private[/COLOR] [COLOR=#011993]Sub[/COLOR] UserForm_Initialize()[/FONT]
[FONT=Menlo]        Me.EnableEvents = [COLOR=#011993]True[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Private Sub CheckBox4_Click()[/FONT]
[FONT=Menlo][COLOR=#011993]If[/COLOR] Me.EnableEvents = [COLOR=#011993]False[/COLOR] [COLOR=#011993]Then[/COLOR][/FONT]
[FONT=Menlo]    [COLOR=#011993]Exit[/COLOR] [COLOR=#011993]Sub[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]
[FONT=Menlo][COLOR=#011993]If[/COLOR] [Check Box 9].Value = 1 [COLOR=#011993]Then[/COLOR][/FONT]
[FONT=Menlo]    Me.EnableEvents = [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo]    Sheets("Proposal RUS").Select[/FONT]
[FONT=Menlo]    Rows("171:184").Select[/FONT]
[FONT=Menlo]    Selection.EntireRow.Hidden = [COLOR=#011993]True
[/COLOR][/FONT][COLOR=#011993][FONT=Menlo]Else[/FONT][/COLOR]
[FONT=Menlo]    [Check Box 9].Value = 0[/FONT]
[FONT=Menlo]    Sheets("Proposal RUS").Select[/FONT]
[FONT=Menlo]    Rows("171:184").Select[/FONT]
[FONT=Menlo]    Selection.EntireRow.Hidden = [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo]    Me.EnableEvents = [COLOR=#011993]True[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]

I'm doing it in a Module sheet and get the "Invalid use of me keyword" on line 8..

Thanks a lot!

 

[mikerickson]

The simplest way to disable user form events is to create a module wide boolean variable and use it to control your events.

Dim DisableUFEvents As Boolean

Private Sub TextBox4_Change()
    If DisableUFEvents Then Exit Sub

    If Not TextBox4.Text like "*'s" Then
        DisableUFEvents = True
        TextBox4.Text = TextBox4.Text & "'s"
    End If
Exit Sub


Private Sub TextBox5_Change()
     If DisableUFEvents Then Exit Sub


' etc.

 

[zakroma]

Thanks a bunch!

But somehow I can't get my code to do what I want it to. In fact it does nothing. (At least there's no error at this time )

Could you please tell where's the error in it:

[COLOR=#011993][FONT=Menlo]Dim[/FONT][/COLOR][FONT=Menlo] DisableUFEvents [/FONT][COLOR=#011993][FONT=Menlo]As[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Boolean[/FONT][/COLOR][FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Private[/COLOR] [COLOR=#011993]Sub[/COLOR] CheckBox7_Click()[/FONT]
[FONT=Menlo]    [COLOR=#011993]If[/COLOR] DisableUFEvents [COLOR=#011993]Then[/COLOR] [COLOR=#011993]Exit[/COLOR] [COLOR=#011993]Sub[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]    [COLOR=#011993]If[/COLOR] [Check Box 7].Value = 1 [COLOR=#011993]Then[/COLOR][/FONT]
[FONT=Menlo]        DisableUFEvents = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]        Sheets("Proposal").Select[/FONT]
[FONT=Menlo]        Rows("171:184").Select[/FONT]
[FONT=Menlo]        Selection.EntireRow.Hidden = [COLOR=#011993]True[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]Else[/FONT][/COLOR]
[FONT=Menlo]       [Check Box 7].Value = 0[/FONT]
[FONT=Menlo]        DisableUFEvents = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]        Sheets("Proposal").Select[/FONT]
[FONT=Menlo]        Rows("171:184").Select[/FONT]
[FONT=Menlo]        Selection.EntireRow.Hidden = [COLOR=#011993]False[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]

 

[zakroma]

OK!

I've fixed it like this:

[COLOR=#011993][FONT=Menlo]Dim[/FONT][/COLOR][FONT=Menlo] DisableUFEvents [/FONT][COLOR=#011993][FONT=Menlo]As[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Boolean[/FONT][/COLOR][FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#011993]Private[/COLOR] [COLOR=#011993]Sub[/COLOR] CheckBox7_Click()[/FONT]
[FONT=Menlo]    [COLOR=#011993]If[/COLOR] DisableUFEvents [COLOR=#011993]Then[/COLOR] [COLOR=#011993]Exit[/COLOR] [COLOR=#011993]Sub[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]    [COLOR=#011993]If[/COLOR] [Check Box 7].Value = 1 [COLOR=#011993]Then[/COLOR][/FONT]
[FONT=Menlo]        DisableUFEvents = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]        Sheets("Proposal RUS").Select[/FONT]
[FONT=Menlo]        Rows("171:184").Select[/FONT]
[FONT=Menlo]        Selection.EntireRow.Hidden = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo][B]        DisableUFEvents = [COLOR=#011993]False[/COLOR][/B][/FONT]
[COLOR=#011993][FONT=Menlo][COLOR=#000000]    [/COLOR]Else[/FONT][/COLOR]
[FONT=Menlo]       [Check Box 7].Value = 0[/FONT]
[FONT=Menlo]        DisableUFEvents = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]        Sheets("Proposal RUS").Select[/FONT]
[FONT=Menlo]        Rows("171:184").Select[/FONT]
[FONT=Menlo]        Selection.EntireRow.Hidden = [COLOR=#011993]False[/COLOR][/FONT]
[FONT=Menlo][B]        DisableUFEvents = [COLOR=#011993]False[/COLOR][/B][/FONT]
[COLOR=#011993][FONT=Menlo][COLOR=#000000]    [/COLOR]End[COLOR=#000000] [/COLOR]If[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]
[/FONT]

But! It still jumps me to the other sheet...

 

[LockeGarmin]

May I ask why you need to disable User Form Events? In this particular case the value of CheckBox7 never gets changed so you shouldn't have to disable the events in the first place.

If you don't want it to jump you to the other sheet you can just remove selecting the other sheet. Does this do what you are expecting?

Option Explicit

Private DisableUFEvents As Boolean


Private Sub CheckBox7_Click()
    
    If DisableUFEvents Then Exit Sub
    Sheets("Proposal RUS").Rows("171:184").EntireRow.Hidden = CheckBox7.Value
    
End Sub

 

[zakroma]

@LockeGarmin Thanks a lot for your tip!

That's a great piece of advice!
Unfortunately it doesn't change anything on the other sheet..

I also tried this to no avail:

[FONT=Menlo][COLOR=#011993]Sub[/COLOR] CheckBox4_Click()[/FONT]
[FONT=Menlo]
    [COLOR=#011993]If[/COLOR] [Check Box 9].Value = 1 [COLOR=#011993]Then[/COLOR][/FONT]
[FONT=Menlo]    Sheets("Proposal RUS").Rows("171:184").EntireRow.Hidden = [COLOR=#011993]True[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo][COLOR=#000000]
    [/COLOR]Else[/FONT][/COLOR]
[FONT=Menlo]    Sheets("Proposal RUS").Rows("171:184").EntireRow.Hidden = [COLOR=#011993]False[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo][COLOR=#000000]
    [/COLOR]End[COLOR=#000000] [/COLOR]If[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]

 

[LockeGarmin]

I'm not sure what 'other sheet' you are talking about besides Proposal RUS sheet.

Are you saying the CheckBox7_Click code I gave you doesn't work?

Also, the CheckBox4_Click method you put in your last reply is testing the value of CheckBox9 (not CheckBox4). Is that intended?

 

[zakroma]

@LockeGarmin

It DOES work!

I've spent so much time trying different options on this macro that the number of the Checkbox has completely slipped my attention!

Correcting it to the actual checkbox I was trying to click did the trick!

THANK YOU!!!