How to temporarily stop textbox change event

jc352

Board Regular
Joined
Aug 13, 2008
Messages
132
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:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,970
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:

Watch MrExcel Video

Forum statistics

Threads
1,127,660
Messages
5,626,152
Members
416,165
Latest member
hamburger138

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
Top