Need help TextBox stuck

Hi Guys, I need your expertise to solve on this issue. From code below, the textBox will stuck after find the duplicate no. Any help will appreciate.

Dim blnRun As Boolean
Private Sub Label29_Click()
Me.Hide
Unload UserForm1
UserForm2.Show
End Sub

Private Sub TextBox2_Change()
Dim rng As Range
Dim lngCtr As Long

If blnRun Then Exit Sub
If Len(TextBox2.Text) = 12 Then

  blnRun = True
  If WorksheetFunction.CountIf(Sheets("data").Range("A:A"), TextBox2.Text) > 0 Then
    MsgBox "This Employee Number already exists"
    TextBox2.Text = ""
    TextBox2.SetFocus
    Exit Sub
   
  Else
    With Sheets("data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      .Value = TextBox2.Text
      .Offset(0, 6).Value = Now
    End With
  End If
    For lngCtr = 2 To 7
    Controls("Label" & lngCtr + 3).Caption = Sheet4.Cells(4, lngCtr).Value
  Next lngCtr
  
  Set rng = Sheets("Data").Range("A1").CurrentRegion.Find(CStr(TextBox2.Value), , xlFormulas, xlPart)
  If Not rng Is Nothing Then
    Label3.Caption = rng.Value
    Label4.Caption = rng.Offset(, 1).Value
    Label28.Caption = rng.Offset(, 5).Value
    Application.Wait Now() + TimeValue("00:00:03")
    Label3.Caption = ""
    Label4.Caption = ""
    Label28.Caption = ""
  End If
  Set rng = Nothing
    TextBox2.Text = ""
  TextBox2.SetFocus
  blnRun = False
End If
Call KeepTime
End Sub

Thank you
Dean

Hi Izandol, yes you are right. Thank you very much.

Hi Nathansav, yes I add some macros at different UF. Still learning in progress.....

Hi Guys, I having problem again with the textbox. Now when I testing using card reader the textbox only appear 11 digit instead of 12. But the digit input at cell is correct 12 digit? This cause my label unable to capture the values. Any idea?

*I do not know whether need to open new thread or continue with this current.

Hi nathasav, I don't get it. The max lenght is set 0 for the textbox & the "" thing. Can you please explain more?

Hi Norie, I've attached the file. If I manually key in the number into textbox, it worked! But if use card reader the number appear only 11 digit. But at cell it show 12 digit?

tq.

Hi Norie, the message showed 48. What is the meaning?

Hi nathasav, yes if key in manually no problem. But if swipe use the card reader it only show 11digit. At cell show value 12 digit.

Thank you
Dean

Dear Team,

I would like to extend the below code

Here i have 2 sheets(1. Editable Pipeline data 2. Trace inputs ) so here i want to compare my Editable Pipeline data with Trace inputs and fetch new data values and update it Editable Pipeline data sheet.

2nd step is do the vlookup from the Editable data to Trace inputs and fetch the other related info


below is the code , here i am getting error becoz i am extending values the data whereas the macros i am not able to extend it

sample excel sheet also attached please help


please helpSub Macro8() ' ' Macro8 Macro ' Myworkings '

' Sheets("Trace Inputs").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlUp).Select Selection.End(xlToRight).Select Range("AR1").Select ActiveSheet.Paste Selection.End(xlToLeft).Select Application.CutCopyMode = False Sheets("Editable Pipeline data").Select Range("B15").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Workings").Select Range("A2").Select ActiveSheet.Paste Range("B2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Masterdata" Range("B2").Select Selection.Copy Range("A2").Select Selection.End(xlDown).Select Range("B177").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Range("B1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Data" Range("B2").Select Sheets("Trace Inputs").Select Range("F2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Workings").Select Range("A2").Select Selection.End(xlDown).Select Range("A178").Select ActiveSheet.Paste Range("B178").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "Present Pipeline" Range("B178").Select Selection.Copy Range("A178").Select Selection.End(xlDown).Select Range("B384").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Selection.End(xlUp).Select Application.CutCopyMode = False Selection.AutoFilter Columns("A:A").Select Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False ActiveSheet.Range("$A$1:$B$384").AutoFilter Field:=2, Criteria1:= _ "Present Pipeline" ActiveSheet.Range("$A$1:$B$384").AutoFilter Field:=1, Criteria1:=RGB(255, _ 255, 255), Operator:=xlFilterCellColor Range("A244").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Editable Pipeline data").Select Selection.End(xlUp).Select Range("B15").Select Selection.End(xlDown).Select Range("B191").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C189").Select Sheets("Workings").Select Range("A1").Select Application.CutCopyMode = False Selection.AutoFilter Columns("A:B").Select Selection.Delete Shift:=xlToLeft Sheets("Editable Pipeline data").Select Range("B190").Select Selection.End(xlUp).Select Range("B15").Select Selection.End(xlDown).Select Range("B190").Select Selection.End(xlUp).Select Range("C15").Select Selection.End(xlDown).Select Range("C191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Trace Inputs'!R2C6:R208C15,10,0)" Range("C191").Select Sheets("Trace Inputs").Select Range("F195").Select Selection.End(xlUp).Select Range("AR1").Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Trace Inputs'!R2C6:R208C44,10,0)" Range("D191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'Trace Inputs'!R2C6:R208C44,11,0)" Range("C191:D191").Select Selection.Cut Range("E191").Select ActiveSheet.Paste Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,14,0)" Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,13,0)" Range("F191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Trace Inputs'!R2C6:R208C44,12,0)" Range("G191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Trace Inputs'!R2C6:R208C44,13,0)" Range("H191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Trace Inputs'!R2C6:R208C44,14,0)" Range("I191").Select Sheets("Trace Inputs").Select Range("AF1").Select Selection.End(xlToLeft).Select Range("F1:AG1").Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'Trace Inputs'!R2C6:R208C44,19,0)" Range("J191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'Trace Inputs'!R2C6:R208C44,28,0)" Range("K191").Select Sheets("Trace Inputs").Select Range("F1:AI1").Select Range(Selection, Selection.End(xlToRight)).Select Sheets("Editable Pipeline data").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'Trace Inputs'!R2C6:R208C44,30,0)" Range("Q191").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'Trace Inputs'!R2C6:R208C44,39,0)" Range("A191").Select Sheets("Trace Inputs").Select Range("AE1").Select Selection.End(xlToLeft).Select Range("F1:AF1").Select Sheets("Editable Pipeline data").Select Range("Q191").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],'Trace Inputs'!R2C6:R208C44,27,0)" Range("A191").Select Selection.Copy Range("A192").Select ActiveSheet.Paste Range("E191:Q191").Select Application.CutCopyMode = False Selection.Copy Range("E192").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("D187").Select Selection.End(xlUp).Select Range("D15").Select Selection.End(xlDown).Select Range("D191").Select End Sub

Guys, when I disable below code, the textbox able to capture all 12 digit. Please advice what when wrong?

'Application.Wait Now() + TimeValue("00:00:03")
    'Label3.Caption = ""
    'Label4.Caption = ""
    'Label28.Caption = ""

tq.

Hi Guys, Forget to mention that have some problem with set focus as well. It only happened for the first swipe only everytime run the program.

Hi nathansav, it just 1 line. Weird.. after amend the textbox maxlenght to 12 now working. Only the set focus will stuck for 1st time swipe only. After that all run well. Any idea/suggestion?

tq
Dean