How to temporarily stop textbox change event

J

jc352

Board Regular
Joined
Aug 13, 2008
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to fill in textboxes based on the value of another textbox. The problem I am having, is that I have a textbox change event for a textbox that will be filled in with when the code runs. Is there a way to stop the change event while that part of the code runs. I've tried using userform1.enableevents = true/false and am having absolutely no luck with it. Here is the code I have so far. If I turn off the textboxes in question which are 2 and 3 it works fine.
Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Private SubTextBox2_Change()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]WithSheets("jobs test")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ' check for existing job<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set c =.Range("B:B").Find(What:=TextBox2.Value, LookIn:=xlValues,LookAt:=xlWhole)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If Not c Is Nothing Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        where = c.Offset(0, -1)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        fillinboxes<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]exists:<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Subtextbox3_change()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]WithSheets("jobs test")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ' check for existing job<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set c =.Range("C:C").Find(What:=TextBox3.Value, LookIn:=xlValues,LookAt:=xlWhole)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If Not c Is Nothing Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        where = c.Offset(0, -2)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        fillinboxes<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]exists:<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Subfillinboxes()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If TextBox2 = "" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lookfor = TextBox3.Value<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set lookrng = Sheets("jobs test").Columns("C")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ElseIf TextBox3 = "" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lookfor = TextBox2.Value<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set lookrng = Sheets("jobs test").Columns("B")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]Set c =lookrng.Find(What:=lookfor, LookIn:=xlValues, LookAt:=xlWhole)<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]        If TextBox2 = "" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'        TextBox2 = c.Offset(0, -1).Value<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        restdaysset = c.Offset(0, 1)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart1 = c.Offset(0, 2)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend1 = c.Offset(0, 3)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart2 = c.Offset(0, 4)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend2 = c.Offset(0, 5)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart3 = c.Offset(0, 6)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend3 = c.Offset(0, 7)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart4 = c.Offset(0, 8)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend4 = c.Offset(0, 9)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart5 = c.Offset(0, 10)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend5 = c.Offset(0, 11)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart6 = c.Offset(0, 12)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend6 = c.Offset(0, 13)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart7 = c.Offset(0, 14)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend7 = c.Offset(0, 15)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        If TextBox3 = "" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'        TextBox3 = c.Offset(0, 1).Value<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        restdaysset = c.Offset(0, 2)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart1 = c.Offset(0, 3)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend1 = c.Offset(0, 4)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart2 = c.Offset(0, 5)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend2 = c.Offset(0, 6)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart3 = c.Offset(0, 7)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend3 = c.Offset(0, 8)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart4 = c.Offset(0, 9)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend4 = c.Offset(0, 10)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart5 = c.Offset(0, 11)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend5 = c.Offset(0, 12)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart6 = c.Offset(0, 13)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend6 = c.Offset(0, 14)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftstart7 = c.Offset(0, 15)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        shiftend7 = c.Offset(0, 16)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]        <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][COLOR=#000000][SIZE=3]
[/SIZE][/COLOR][/FONT]
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
To control userform events, I introduce a boolean argument DisableMyEvents and test for it at the start of my event code.

Code: 
' in userform code module

Dim DisableMyEvents as Boolean

Private Sub TextBox1_Change()
    If DisableMyEvents then Exit Sub
    
    ' code
End Sub

Private Sub CommandButton1_Click()
    DisableMyEvents = True
    TextBox1.Text = "test"
    DisableMyEvents = False
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Find exact match in column not working
Replies
3
Views
559
tonyyy
tonyyy

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top