Previous Topic Next Topic

Pop message into textbox on userform if number is exceeded

Solved/Closed
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Aug 2, 2021 at 04:20 PM
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Aug 24, 2021 at 04:31 AM
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
Related:

6 responses

Answer 1 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 3, 2021 at 12:15 PM
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
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Aug 3, 2021 at 02:47 PM
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
0
Answer 2 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Aug 16, 2021 at 12:05 PM
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
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Updated on Aug 17, 2021 at 04:10 AM
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 .
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Aug 17, 2021 at 11:25 AM
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
0
Answer 3 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 19, 2021 at 12:10 PM
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
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Aug 22, 2021 at 05:11 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Aug 23, 2021 at 11:58 AM
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
0
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Updated on Aug 24, 2021 at 04:32 AM
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 .
0
Answer 4 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 5, 2021 at 11:50 AM
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
0
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Aug 5, 2021 at 04:39 PM
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 9, 2021 at 12:14 PM
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
0
Answer 6 / 6
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Updated on Aug 18, 2021 at 04:41 AM
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
0

Similar discussions

cannot save attachment for yahoo email
joesoui -
nisva -

1 response
Someone hacked into my Facebook account
Suzanne -
Ambucias -

1 response