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
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Aug 24, 2021 at 04:31 AM
Related:
- Pop message into textbox on userform if number is exceeded
- Voice message downloader - Guide
- Hunie pop free - Download - Adult games
- Send popup message to another computer on network windows 10 - Guide
- Do message requests on instagram expire - Guide
- Viber pop up after call - Guide
6 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 3, 2021 at 12:15 PM
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?:
Best regards,
Trowa
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
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
Updated on Aug 16, 2021 at 12:05 PM
Hi Hasson,
Here is the code for Combobox1, 2, 3, Textbox1 and the CommandButton1:
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
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
Posts
18
Registration date
Monday August 2, 2021
Status
Member
Last seen
May 23, 2022
Updated on Aug 17, 2021 at 04:10 AM
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
thanks for your effort , but it gives error " ShowAllData method of Worksheet class failed" int this line
ActiveSheet.ShowAllDataand 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 .
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
Aug 17, 2021 at 11:25 AM
Hi Hasson,
Both points have been addressed in the code below:
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 19, 2021 at 12:10 PM
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:
into
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:
into:
Best regards,
Trowa
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
Hasson_6770
Posts
18
Registration date
Monday August 2, 2021
Status
Member
Last seen
May 23, 2022
Aug 22, 2021 at 05:11 AM
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
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
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
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
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
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
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
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 .
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 .
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 5, 2021 at 11:50 AM
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
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
Hasson_6770
Posts
18
Registration date
Monday August 2, 2021
Status
Member
Last seen
May 23, 2022
Aug 5, 2021 at 04:39 PM
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
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
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 9, 2021 at 12:14 PM
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
Ok, great.
Working on it. I'm a bit rusty doing userform stuff though. Hopefully you are not in a hurry.
Best regards,
Trowa
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
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
this my updating code
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
Aug 3, 2021 at 02:47 PM
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