While doing entry using userform we find that the value in TextBox2 is converted to blank, following are the codes we are using in the userform. Due to which the users are not able to do entry in the file. Please help.
Private Sub ComboBox3_Change()
Dim found As Range
Set found = Sheets(1).Columns(51).Find(What:=UserForm2.TextBox1.value & ComboBox2.value & ComboBox3.value)
If UserForm2.ComboBox3.value <> "" Then
If found Is Nothing Then Exit Sub
UserForm2.TextBox3.value = Sheets(1).Cells(found.Row, found.Column).Offset(0, -45).value
Else
UserForm2.TextBox3.value = ""
End If
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub ComboBox3_DropButtonClick()
ar = Sheets(1).Range("A2:ax2000")
If IsNumeric(Application.Match(TextBox1.value & ComboBox2.value, Application.Index(ar, 0, 50), 0)) Then
For j = 1 To UBound(ar)
If ar(j, 50) = TextBox1.value & ComboBox2.value And InStr(c00, ar(j, 3)) = 0 Then c00 = c00 & "|" & ar(j, 3)
Next
ComboBox3.List = Application.Transpose(Split(Mid(c00, 2), "|"))
End If
End Sub
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub TextBox2_AfterUpdate()
If UserForm2.TextBox2.value > UserForm2.TextBox3.value Then
TextBox2.value = ""
End If
End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
If ComboBox2 <> "" And ComboBox3 <> "" And Format(IsNumeric(TextBox3), "0.00") >= Format(IsNumeric(TextBox2), "0.00") And TextBox2 <> "" Then
ActiveCell = UCase(ComboBox2)
ActiveCell.Offset(, 1) = ComboBox3
ActiveCell.Offset(, 2) = TextBox2.value
Unload Me
Else
MsgBox "Please enter the Full Data!", vbCritical, ""
Exit Sub
End If
Columns("E:F").AutoFit
Dim found1 As Range
Set found1 = Sheets(1).Columns(51).Find(What:=ActiveCell.Offset(0, -3).value & ActiveCell.value & ActiveCell.Offset(0, 1).value)
If Not found1 Is Nothing Then
Sheets(1).Cells(found1.Row, found1.Column).Offset(0, -46).value = Sheets(1).Cells(found1.Row, found1.Column).Offset(0, -46).value + ActiveCell.Offset(0, 2).value
End If
Dim i2, Lrw2 As Long
Lrw2 = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row
For i2 = Lrw2 To 5 Step -1
If Sheets(2).Cells(i2, 7) <> "" Then
If Sheets(2).Cells(i2, 7).value < Sheets(2).Cells(i2, 4).value Then
Sheets(2).Cells(i2, 4).Offset(1, 0).EntireRow.Insert
Sheets(2).Range(Cells(i2 + 1, 2), Cells(i2 + 1, 4)).FillDown
Sheets(2).Range(Cells(i2 + 1, 8), Cells(i2 + 1, 9)).FillDown
Sheets(2).Range("D" & i2).Offset(1, 0).value = Sheets(2).Range("d" & i2).value - Sheets(2).Range("g" & i2).value
Sheets(2).Range("d" & i2).value = Sheets(2).Range("g" & i2).value
End If
End If
Next i2
Dim cell As Range
Dim rowcounter As Integer
For Each cell In Range("A5", Cells(Rows.Count, 1).End(xlUp))
rowcounter = rowcounter + 1
cell.value = rowcounter
Next
End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub ComboBox3_Change()
Dim found As Range
Set found = Sheets(1).Columns(51).Find(What:=UserForm2.TextBox1.value & ComboBox2.value & ComboBox3.value)
If UserForm2.ComboBox3.value <> "" Then
If found Is Nothing Then Exit Sub
UserForm2.TextBox3.value = Sheets(1).Cells(found.Row, found.Column).Offset(0, -45).value
Else
UserForm2.TextBox3.value = ""
End If
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub ComboBox3_DropButtonClick()
ar = Sheets(1).Range("A2:ax2000")
If IsNumeric(Application.Match(TextBox1.value & ComboBox2.value, Application.Index(ar, 0, 50), 0)) Then
For j = 1 To UBound(ar)
If ar(j, 50) = TextBox1.value & ComboBox2.value And InStr(c00, ar(j, 3)) = 0 Then c00 = c00 & "|" & ar(j, 3)
Next
ComboBox3.List = Application.Transpose(Split(Mid(c00, 2), "|"))
End If
End Sub
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub TextBox2_AfterUpdate()
If UserForm2.TextBox2.value > UserForm2.TextBox3.value Then
TextBox2.value = ""
End If
End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
If ComboBox2 <> "" And ComboBox3 <> "" And Format(IsNumeric(TextBox3), "0.00") >= Format(IsNumeric(TextBox2), "0.00") And TextBox2 <> "" Then
ActiveCell = UCase(ComboBox2)
ActiveCell.Offset(, 1) = ComboBox3
ActiveCell.Offset(, 2) = TextBox2.value
Unload Me
Else
MsgBox "Please enter the Full Data!", vbCritical, ""
Exit Sub
End If
Columns("E:F").AutoFit
Dim found1 As Range
Set found1 = Sheets(1).Columns(51).Find(What:=ActiveCell.Offset(0, -3).value & ActiveCell.value & ActiveCell.Offset(0, 1).value)
If Not found1 Is Nothing Then
Sheets(1).Cells(found1.Row, found1.Column).Offset(0, -46).value = Sheets(1).Cells(found1.Row, found1.Column).Offset(0, -46).value + ActiveCell.Offset(0, 2).value
End If
Dim i2, Lrw2 As Long
Lrw2 = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row
For i2 = Lrw2 To 5 Step -1
If Sheets(2).Cells(i2, 7) <> "" Then
If Sheets(2).Cells(i2, 7).value < Sheets(2).Cells(i2, 4).value Then
Sheets(2).Cells(i2, 4).Offset(1, 0).EntireRow.Insert
Sheets(2).Range(Cells(i2 + 1, 2), Cells(i2 + 1, 4)).FillDown
Sheets(2).Range(Cells(i2 + 1, 8), Cells(i2 + 1, 9)).FillDown
Sheets(2).Range("D" & i2).Offset(1, 0).value = Sheets(2).Range("d" & i2).value - Sheets(2).Range("g" & i2).value
Sheets(2).Range("d" & i2).value = Sheets(2).Range("g" & i2).value
End If
End If
Next i2
Dim cell As Range
Dim rowcounter As Integer
For Each cell In Range("A5", Cells(Rows.Count, 1).End(xlUp))
rowcounter = rowcounter + 1
cell.value = rowcounter
Next
End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------