pop message into textbox on userform if number is exceeded Solved/Closed

Question

hello
I  have   userform  contains  multiple comboboxes   and  textboxes   . the  comboboxes1,2,3   should  match   with   sheet  data  in COL B,C,D   and  textbox1    should  match with COL D .
the   COL D   contains  values   if   I  write  value  bigger  than   what  is  existed IN COL D  then  should show  message " the  number  is  exceeded, available QTY is .... please  try  again "  then  exit  and  if  the value  is  smaller  then  copy  to   the  bottom  in sheet result . the  same  thing  with compopox4,5,6   should  match  with col B,C,D   and  textbox  2   with  col D  and   compobox 7,8,9 should  match  with col B,C,D  and  textbox3  with  COL D

TrowaD · Answer

Hi Hasson,

Not sure what the point of the comboboxes is, so let me just stick to the request; when you enter a number in textbox1, check to see if it exceed any number in column D. 
When it does: display message.
When it doesn't: add number to the bottom of column D.

Here is the code for textbox1?:
Private Sub TextBox1_Change()
Dim mVal As Long
mVal = Application.WorksheetFunction.Max(Columns("D"))
If TextBox1.Value > mVal Then
    MsgBox "The number is exceeded, available QTY is " & mVal & " please try again."
Else
    Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1.Value
End If
End Sub 

Best regards,
Trowa

TrowaD · Answer

Hi Hasson,

Let me see if I understand your setup.

Col A - Col B - Col C - Col D
aaa - 123 - abc - 12
aaa - 123 - abc - 15
aaa - 123 - def - 18
aaa - 456 - ghi - 21

When you select aaa in combobox1, you get the option to select 123 and 456 in combobox2.
 When you select 123 in combobox2, you get the option to select abc and def in combobox3.
When you select abc in combobox3 and enter 20 in textbox1, then you want a message.
But when you enter 10 in textbox1, you want the values aaa, 123, abc and 10 to be added to the bottom of the list.

Does that sound right?

If not, could you post a small sample for better understanding.

Best regards,
Trowa

TrowaD · Answer

Hi Hasson,

Ok, great.
 
Working on it. I'm a bit rusty doing userform stuff though. Hopefully you are not in a hurry.

Best regards,
Trowa

TrowaD · Answer

Hi Hasson,

Here is the code for Combobox1, 2, 3, Textbox1 and the CommandButton1:
Private Sub ComboBox1_Change()
UserForm1.ComboBox2.Clear

ActiveSheet.Range("$A$1:$D$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Copy Range("Z1")
ActiveSheet.ShowAllData
Range("Z1:Z" & Range("Z" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

For Each cell In Range("Z2:Z" & Range("Z" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    UserForm1.ComboBox2.AddItem cell.Value
Next

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Columns("Z").ClearContents
End Sub

Private Sub ComboBox2_Change()
If ComboBox2.Value = vbNullString Then Exit Sub
UserForm1.ComboBox3.Clear

ActiveSheet.Range("$A$1:$D$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=ComboBox1.Value
ActiveSheet.Range("$A$1:$D$" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=ComboBox2.Value
Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy Range("Z1")
ActiveSheet.ShowAllData
Range("Z1:Z" & Range("Z" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

For Each cell In Range("Z2:Z" & Range("Z" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    UserForm1.ComboBox3.AddItem cell.Value
Next

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Columns("Z").ClearContents
End Sub

Private Sub CommandButton1_Click()
Dim mVal, lRow As Long
Dim tbVal As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
mVal = Application.WorksheetFunction.MaxIfs(Range("D2:D" & lRow), Range("A2:A" & lRow), ComboBox1.Value, _
Range("B2:B" & lRow), ComboBox2.Value, Range("C2:C" & lRow), ComboBox3.Value)
tbVal = TextBox1.Value

If tbVal > mVal Then
    MsgBox "The number is exceeded, available QTY is " & mVal & ", please try again."
Else
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox1.Value
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox2.Value
    Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox3.Value
    Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = tbVal
End If
End Sub

Private Sub UserForm_Initialize()
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    UserForm1.ComboBox1.AddItem cell.Value
Next
ActiveSheet.ShowAllData
End Sub 

Check to see if it works as intended, then apply it to the other Combo- and TextBoxes if you want (I don't see a reason why you would).

As always, let us know if you get stuck.

Best regards,
Trowa

Hasson_6770 · Answer

Hi Trowa,
that's  great !  I  try  applying  with  the  others  comboboxes ,  but  it  gives  me  error  mismatch in this  line 
tbVal2 = TextBox2.Value
this  my  updating  code 
Private Sub CommandButton1_Click()Dim mVal, mVal1, lRow, tbVal, tbVal2 As LonglRow = Range("A" & Rows.Count).End(xlUp).RowmVal = Application.WorksheetFunction.MaxIfs(Range("D2:D" & lRow), Range("A2:A" & lRow), ComboBox1.Value, _Range("B2:B" & lRow), ComboBox2.Value, Range("C2:C" & lRow), ComboBox3.Value)mVal1 = Application.WorksheetFunction.MaxIfs(Range("D2:D" & lRow), Range("A2:A" & lRow), ComboBox4.Value, _Range("B2:B" & lRow), ComboBox5.Value, Range("C2:C" & lRow), ComboBox6.Value)tbVal = TextBox1.ValuetbVal2 = TextBox2.ValueIf tbVal > mVal Or tbVal2 > mVal1 Then    MsgBox "The number is exceeded, available QTY is " & mVal & ", please try again."Else   Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox1.Value    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox2.Value   Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox3.Value    Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = tbValEnd IfEnd Sub

TrowaD · Answer

Hi Hasson, Then you probably left TextBox2 empty. Add an IF statement to make sure it's not. I'm also noticing that by changing tbVal from Integer to Long it is not recognised as a number anymore. Change: tbVal = TextBox1.Value tbVal2 = TextBox2.Value into If TextBox1.Value <> vbNullString Then tbVal = CInt(TextBox1.Value) If TextBox2.Value <> vbNullString Then tbVal2 = CInt(TextBox2.Value) To not get confused at which number is exceeded, you might want to change the second part. I also added another IF statement to not add a 0 when the ComboBoxes are empty: If tbVal > mVal Or tbVal2 > mVal1 Then MsgBox "The number is exceeded, available QTY is " & mVal & ", please try again." Else Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox1.Value Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox2.Value Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox3.Value Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = tbVal End If into: If tbVal > mVal Then MsgBox "The number is exceeded, available QTY for: " & vbNewLine & ComboBox1.Value & " - " & ComboBox2.Value _ & " - " & ComboBox3.Value & vbNewLine & "is " & mVal & ", please try again." ElseIf tbVal <> 0 Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox1.Value Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox2.Value Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox3.Value Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = tbVal End If If tbVal2 > mVal2 Then MsgBox "The number is exceeded, available QTY for: " & vbNewLine & ComboBox4.Value & " - " & ComboBox5.Value _ & " - " & ComboBox6.Value & vbNewLine & "is " & mVal2 & ", please try again." ElseIf tbVal2 <> 0 Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox4.Value Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox5.Value Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = ComboBox6.Value Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = tbVal2 End If Best regards, Trowa

Pop message into textbox on userform if number is exceeded

6 responses

Similar discussions

Subscribe To Our Newsletter!