Pop message into textbox on userform if number is exceeded

[Solved]
Report
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021
-
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021
-
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

6 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Trowa
you answer a half question . so about the combobox 1,2,3 contain ITEMS . it should match with col A,B,C and when fill textbox 1 if the value is bigger then pop the message and show the value is related for items from selected combobox1,2,3 . the same thing when select from combobox 4,5,6 and match with COL A,B,C and when fill the value in textbox2 if it's bigger then should pop the message .
best regards,
Hasson
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Trowa,
thanks for your effort , but it gives error " ShowAllData method of Worksheet class failed" int this line
ActiveSheet.ShowAllData
and I disabled this line and run the form and tested .it works but when I put a big number in textbox like 150000, 345677 .it gives error overflow , any help to fix theses , please?problems .
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486 >
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Hasson,

Both points have been addressed in the code below:
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, tbVal As Long

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
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub 

Best regards,
Trowa
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Trowa,

thanks but I no know what happens . from the first time works perfectly but sometimes shows me error over flow in this line
tbVal = CInt(TextBox1.Value)   

to understand what I did exactly when fill all the comboboxes and textboxes and I tested by just fill combobox1,2,3, textbox1 or just fill combobox4,5,6 ,textbox2 it works well. the same cases often give error .
thanks again
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486 >
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Hasson,

I thought CInt changes a string into a number, but it actually redims the value as integer, thus limiting the max value to 32767.

Change CInt into CLng to increase the max value to 2147483647.

That was the only error you were talking about, right?

Best regards,
Trowa
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021
>
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021

That was the only error you were talking about, right?
yes
just I would make your attenion about typo mVal doesn't show the value in message . it should be mVal1 about combobox4,5,6

thanks very much for your assistance .
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

Hi Trowa,
yes this is exactly what I'm talking about , but I would apply the same thing with combobox4,5,6 , textbox2 and combobox 7,8,9 , textbox3 like what you explained what happens with combobox1,2,3 , textbox1
thanks again
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
Posts
14
Registration date
Monday August 2, 2021
Status
Member
Last seen
September 23, 2021

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 Long
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)
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.Value
tbVal2 = TextBox2.Value

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
End Sub