VBA - My TextBox_Change Sub makes the form unload slow?

NessPJ

Active Member
Joined
May 10, 2011
Messages
414
Office Version
  1. 365
Hi all,

I am working on a project with a userform and it contains some data entry by the user, by the form of the barcode.
To show the user, the barcode they are dissecting (and give them some interactivity) i use the TextBox_Change routine posted below.

Though i noticed that this seems to make the unload of my Userform really slow! When i cut out the TextBoxInvoer_Change() subroutine the
cancel/unloading of the form is instantaneous again.

Is this a known thing to happen? Is there anything i can/should do to improve performance?


VBA Code:
Private Sub TextBoxInvoer_Change()

If InStr(1, TextBoxInvoer.Text, AppIDBatch) > 0 Then
    BatchDetect = InStr(1, TextBoxInvoer.Text, AppIDBatch)
    LabelBatchData.Caption = Mid(TextBoxInvoer.Text, BatchDetect + 4, BatchLength)
Else
    LabelBatchData.Caption = ""
End If

If InStr(1, TextBoxInvoer.Text, AppIDTHT) > 0 Then
    THTDetect = InStr(1, TextBoxInvoer.Text, AppIDTHT)
    LabelTHTData.Caption = Mid(TextBoxInvoer.Text, THTDetect + 4, THTLength)
End If

If InStr(1, TextBoxInvoer.Text, AppIDSSCC) > 0 Then
    SSCCDetect = InStr(1, TextBoxInvoer.Text, AppIDSSCC)
    LabelSSCCData.Caption = Mid(TextBoxInvoer.Text, SSCCDetect + 4, SSCCLength)
End If
    
End Sub

Private Sub CancelButton_Click()

i = 5

Unload Me

Sheets("Menu").Activate

Sheets("Menu").Range("H5").ClearContents
Sheets("Menu").Range("H6").ClearContents
Sheets("Menu").Range("H8").ClearContents
Sheets("Menu").Range("H9").ClearContents
Sheets("Menu").Range("H11").ClearContents
Sheets("Menu").Range("H12").ClearContents
Sheets("Menu").Range("K5").ClearContents
Sheets("Menu").Range("K6").ClearContents
Sheets("Menu").Range("K8").ClearContents
Sheets("Menu").Range("K9").ClearContents

MsgBox "U heeft de bewerking afgebroken.", vbCritical, "Bewerking afgebroken"

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Where are the several AppIDxyz calculated, and how?
 
Upvote 0
Where are the several AppIDxyz calculated, and how?

They are fixed values which are retrieved by the Subroutine (before it loads the Userform).

VBA Code:
Option private module

   Public AppIDBatch As String, BatchLength As String, AppIDTHT As String, THTLength As String, DataCheckINV As String, AppIDSSCC As String, SSCCLength As String

Private Sub Input()

AppIDBatch = Sheets("Parameters").Range("C12").Value
BatchLength = Sheets("Parameters").Range("C13").Value

AppIDTHT = Sheets("Parameters").Range("C14").Value
THTLength = Sheets("Parameters").Range("C15").Value

AppIDSSCC = Sheets("Parameters").Range("C16").Value
SSCCLength = Sheets("Parameters").Range("C17").Value

Start:      '//Start van de routine

i = 1

For i = 1 To 5

    FormLokPal = LokPalArray(i - 1)
    FormPal = i
    
Invoer.Show

Next

'Rest of my VBA code

End Sub
 
Upvote 0
It looks like you intend to work with numbers (eg considering the Mid () statement) but you have declared the affected variables as a string.
This can lead to undesirable effects because: numeric 1 + 4 equals 5, alphanumeric 1 + 4 equals 14.
It is also wise to first let all the code in the UserForm do its work before closing it by removing it from memory (Unload Me).
Finally, the Unload Me triggers the UserForm_Terminate event procedure, so it is recommended that you only perform strictly necessary actions there.
Not sure if this will improve performance, just to avoid mysterious code behavior :)


VBA Code:
    Sheets("Menu").Range("K9").ClearContents

    MsgBox "U heeft de bewerking afgebroken.", vbCritical, "Bewerking afgebroken"

    Unload Me    '<<<<<<<<
End Sub
 
Upvote 0
Just for testing, add these "Debug.Print" to your code:
VBA Code:
Private Sub TextBoxInvoer_Change()
Debug.Print "Invoer_Change --->", Timer
If InStr(1, TextBoxInvoer.Text, AppIDBatch) > 0 Then
'code continues
VBA Code:
Private Sub CancelButton_Click()
Debug.Print "Cancel Click >>>>", Timer
i = 5

Unload Me
Debug.Print "Cancel Click B >>>", Timer

Sheets("Menu").Activate

Sheets("Menu").Range("H5").ClearContents
Sheets("Menu").Range("H6").ClearContents
Sheets("Menu").Range("H8").ClearContents
Sheets("Menu").Range("H9").ClearContents
Sheets("Menu").Range("H11").ClearContents
Sheets("Menu").Range("H12").ClearContents
Sheets("Menu").Range("K5").ClearContents
Sheets("Menu").Range("K6").ClearContents
Sheets("Menu").Range("K8").ClearContents
Sheets("Menu").Range("K9").ClearContents
Debug.Print "Cancel Click C >>>", Timer

MsgBox "U heeft de bewerking afgebroken.", vbCritical, "Bewerking afgebroken"

End Sub
Then do some job, and close the userform

At that point go to the editor of the macro and open the "Immediate Window" (contr-g should do the job; or use Menu /View /Immediate window)

Copy the last 10-20 lines from the bottom and insert into your next message.

Bye
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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