Logical Error in execution of code (Textbox & CommandButton - Userform)

deba2020

New Member
Joined
Jan 8, 2020
Messages
12
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1632476188829.png



1632476229877.png
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,795
Office Version
  1. 2010
Platform
  1. Windows
Change this:

Rich (BB code):
Private Sub TextBox2_AfterUpdate()
If Val(UserForm2.TextBox2.Value) > Val(UserForm2.TextBox3.Value) Then
TextBox2.Value = ""
End If
End Sub
 
Solution

deba2020

New Member
Joined
Jan 8, 2020
Messages
12
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Thank you DanteAmor very much, it works perfectly now.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,795
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,243
Messages
5,836,205
Members
430,406
Latest member
pmav

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