Pop Out box if the data is not within the range

kimberly090

Board Regular
Joined
May 22, 2014
Messages
99
Hi,
I have develop a userform inside my excel workbook.

I wish to have a alert message pop out on submit button is clicked when meet the following requirement:

1. If combobox2 value equals to Ni, the value in combobox6 need to within 50T, 30U, 20A and 100

2. If value in combobox6 is not within those value, when user hit the submit button, it will show a message box to alert user that the value for combobox6 is not within the specs, if user choose on Yes, then excel will store the data, if user select No, user will require to re-select the data.

My userform code will be look like this:

Code:
Private Sub CommandButton1_Click()    
    Sheets("Overall").Activate
    
    With Me
        If Len(.ComboBox1.Value) * Len(.TextBox2.Value) * Len(.TextBox3.Value) * Len(.TextBox1.Value) * Len(.ComboBox7.Value) * Len(.ComboBox3.Value) * Len(.TextBox4.Value) * Len(.TextBox5.Value) * Len(.ComboBox4.Value) * Len(.ComboBox6.Value) * Len(.ComboBox5.Value) * Len(.TextBox7.Value) * Len(.TextBox8.Value) * Len(.TextBox6.Value) * Len(.ComboBox2.Value) * Len(.TextBox9.Value) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
        Else
            If TextBox8.Value > 3 Then
    If MsgBox("TextBox8 > 3" & vbCrLf & "Continue?", vbYesNo) = vbNo Then
        MsgBox "Please change the value of TextBox8"
        TextBox8.SetFocus
    Else


                          
            eRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 2).Value = ComboBox1.Text
            Cells(eRow, 5).Value = TextBox1.Text
            Cells(eRow, 1).Value = ComboBox7.Text
            Cells(eRow, 6).Value = ComboBox3.Text
            Cells(eRow, 15).Value = ComboBox2.Text
            Cells(eRow, 17).Value = TextBox2.Text
            Cells(eRow, 18).Value = TextBox3.Text
            Cells(eRow, 9).Value = ComboBox4.Text
            Cells(eRow, 11).Value = ComboBox5.Text
            Cells(eRow, 7).Value = TextBox4.Text
            Cells(eRow, 8).Value = TextBox5.Text
            Cells(eRow, 14).Value = TextBox6.Text
            Cells(eRow, 16).Value = ComboBox6.Text
            Cells(eRow, 12).Value = TextBox7.Text
            Cells(eRow, 13).Value = TextBox8.Text
            Cells(eRow, 19).Value = TextBox9.Text
        End If
         End If
        End If
    End With
End Sub
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
not tested but you could try something like this:

Code:
Private Sub CommandButton1_Click()
    Dim m As Variant, RequiredRange As Variant
    Dim msg As Integer
    
    RequiredRange = Array("50T", "30U", "20A", "100")
    
    If Me.ComboBox2.Value = "Ni" Then
        m = Application.Match(ComboBox6.Value, RequiredRange, False)
        If IsError(m) Then
        
        msg = MsgBox(ComboBox6.Value & Chr(10) & _
         "Selection Value Out Of Range" & Chr(10) & Chr(10) & _
         "Do You Want To Continue With Submission?", 36, "Warning")
         
        If msg = 7 Then Me.ComboBox6.SetFocus: Exit Sub
        End If
    End If
    
    'rest of code
    


    
End Sub

Adjust code as required to meet specific project need.

Hope Helpful

Dave
 
Upvote 0
Hi,
I have modified my code become like this
but when the plating rate is less than 3, the data will not store inside the excel sheet.

Code:
Private Sub CommandButton1_Click()    
    Sheets("Overall").Activate
    
    Dim m As Variant, RequiredRange As Variant
    Dim msg As Integer
    
        RequiredRange1 = Array("30S", "30A", "40S")
        RequiredRange2 = Array("10A", "15S", "15A", "20S")
        RequiredRange3 = Array("30S", "30A", "40S")
    
    If Me.ComboBox2.Value = "NiPd" Then
    m = Application.Match(ComboBox6.Value, RequiredRange1, False)
        If IsError(m) Then
        
        msg = MsgBox("Stabilizer Reading:" & ComboBox6.Value & Chr(10) & _
         "Selection Value Out Of Range" & Chr(10) & Chr(10) & _
         "Do You Want To Continue With Submission?", 36, "Warning")
         
        If msg = 7 Then Me.ComboBox6.SetFocus: Exit Sub
        End If
        End If
    
    
       If Me.ComboBox2.Value = "NiAu" Then
          
        m = Application.Match(ComboBox6.Value, RequiredRange2, False)
        If IsError(m) Then
        
        msg = MsgBox("Stabilizer Reading:" & ComboBox6.Value & Chr(10) & _
         "Selection Value Out Of Range" & Chr(10) & Chr(10) & _
         "Do You Want To Continue With Submission?", 36, "Warning")
         
        If msg = 7 Then Me.ComboBox6.SetFocus: Exit Sub
        End If
        End If
        
        If Me.ComboBox2.Value = "NiPdAu" Then
          
        m = Application.Match(ComboBox6.Value, RequiredRange3, False)
        If IsError(m) Then
        
        msg = MsgBox("Stabilizer Reading:" & ComboBox6.Value & Chr(10) & _
         "Selection Value Out Of Range" & Chr(10) & Chr(10) & _
         "Do You Want To Continue With Submission?", 36, "Warning")
         
        If msg = 7 Then Me.ComboBox6.SetFocus: Exit Sub
        End If
        End If
    
    With Me
        If Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox7.Value) * Len(.ComboBox3.Value) * Len(.ComboBox2.Value) * Len(.TextBox2.Value) * Len(.TextBox3.Value) * Len(.ComboBox4.Value) * Len(.ComboBox5.Value) * Len(.TextBox4.Value) * Len(.TextBox5.Value) * Len(.TextBox6.Value) * Len(.ComboBox6.Value) * Len(.TextBox7.Value) * Len(.TextBox8.Value) * Len(.TextBox9.Value) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
        Else
            
            If CSng(.TextBox8.Text) > 3 Then
                If MsgBox("Plating Rate below than 3.0 um, Kindly stop production and use another Ni Bath" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbNo Then
                          
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Warning"
                        
            If CSng(.TextBox8.Text) = 3.2 Then
                If MsgBox("Plating Rate below than 3.2 um , Standby the next Ni bath and start heat up to 65°" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbNo Then
                          
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Warning"
                    
                    
                    Exit Sub
                 End If
                          End If
             End If
                    
            eRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 2).Value = ComboBox1.Text
            Cells(eRow, 5).Value = TextBox1.Text
            Cells(eRow, 1).Value = ComboBox7.Text
            Cells(eRow, 6).Value = ComboBox3.Text
            Cells(eRow, 15).Value = ComboBox2.Text
            Cells(eRow, 17).Value = TextBox2.Text
            Cells(eRow, 18).Value = TextBox3.Text
            Cells(eRow, 9).Value = ComboBox4.Text
            Cells(eRow, 11).Value = ComboBox5.Text
            Cells(eRow, 7).Value = TextBox4.Text
            Cells(eRow, 8).Value = TextBox5.Text
            Cells(eRow, 14).Value = TextBox6.Text
            Cells(eRow, 16).Value = ComboBox6.Text
            Cells(eRow, 12).Value = TextBox7.Text
            Cells(eRow, 13).Value = TextBox8.Text
            Cells(eRow, 19).Value = TextBox9.Text
             End If
        End If
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top