Pop out message box to display data

K

kimberly090

Board Regular
Joined
May 22, 2014
Messages
99
Hi,

I have my submit button code like this:

Code: 
Private Sub CommandButton1_Click()Sheets("Overall").Activate
                   With Me
                           If Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox2.Value) * Len(.ComboBox3.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) = 0 Then
            MsgBox "Please Complete All Fields Before Submit"
            
        Else
        
            eRow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = ComboBox1.Text
            Cells(eRow, 2).Value = TextBox2.Text
            Cells(eRow, 3).Value = TextBox3.Text
            Cells(eRow, 4).Value = TextBox1.Text
            Cells(eRow, 5).Value = ComboBox3.Text
            Cells(eRow, 6).Value = TextBox4.Text
            Cells(eRow, 7).Value = TextBox5.Text
            Cells(eRow, 8).Value = ComboBox4.Text
            Cells(eRow, 15).Value = ComboBox6.Text
            Cells(eRow, 10).Value = ComboBox5.Text
            Cells(eRow, 11).Value = TextBox7.Text
            Cells(eRow, 12).Value = TextBox8.Text
            Cells(eRow, 13).Value = TextBox6.Text
            Cells(eRow, 14).Value = ComboBox2.Text
            
            End If
    End With
End Sub

I wish to add on a confirmation pop out message box to alert user that if the value which is being key in in TextBox8 is exceed 3.0

If user select Yes, then only will store the data inside excel sheet, but if the user select no, another pop out message box will be display to inform user to re-type the value in TextBox8.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Try something like this...

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    
    Sheets("Overall").Activate
    
    [color=darkblue]With[/color] Me
        [color=darkblue]If[/color] Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox2.Value) * Len(.ComboBox3.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) = 0 [color=darkblue]Then[/color]
            MsgBox "Please Complete All Fields Before Submit"
        [color=darkblue]Else[/color]
            
            [color=darkblue]If[/color] [color=darkblue]CSng[/color](.TextBox8.Text) > 3 [color=darkblue]Then[/color]
                
                [color=darkblue]If[/color] MsgBox("TextBox8 exceeds 3.0" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbYes [color=darkblue]Then[/color]
                    
                    eRow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                    Cells(eRow, 1).Value = ComboBox1.Text
                    Cells(eRow, 2).Value = TextBox2.Text
                    Cells(eRow, 3).Value = TextBox3.Text
                    Cells(eRow, 4).Value = TextBox1.Text
                    Cells(eRow, 5).Value = ComboBox3.Text
                    Cells(eRow, 6).Value = TextBox4.Text
                    Cells(eRow, 7).Value = TextBox5.Text
                    Cells(eRow, 8).Value = ComboBox4.Text
                    Cells(eRow, 15).Value = ComboBox6.Text
                    Cells(eRow, 10).Value = ComboBox5.Text
                    Cells(eRow, 11).Value = TextBox7.Text
                    Cells(eRow, 12).Value = TextBox8.Text
                    Cells(eRow, 13).Value = TextBox6.Text
                    Cells(eRow, 14).Value = ComboBox2.Text
                    
                [color=darkblue]Else[/color]
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Title"
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Try something like this...

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    
    Sheets("Overall").Activate
    
    [COLOR=darkblue]With[/COLOR] Me
        [COLOR=darkblue]If[/COLOR] Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox2.Value) * Len(.ComboBox3.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) = 0 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Please Complete All Fields Before Submit"
        [COLOR=darkblue]Else[/COLOR]
            
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]CSng[/COLOR](.TextBox8.Text) > 3 [COLOR=darkblue]Then[/COLOR]
                
                [COLOR=darkblue]If[/COLOR] MsgBox("TextBox8 exceeds 3.0" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbYes [COLOR=darkblue]Then[/COLOR]
                    
                    eRow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                    Cells(eRow, 1).Value = ComboBox1.Text
                    Cells(eRow, 2).Value = TextBox2.Text
                    Cells(eRow, 3).Value = TextBox3.Text
                    Cells(eRow, 4).Value = TextBox1.Text
                    Cells(eRow, 5).Value = ComboBox3.Text
                    Cells(eRow, 6).Value = TextBox4.Text
                    Cells(eRow, 7).Value = TextBox5.Text
                    Cells(eRow, 8).Value = ComboBox4.Text
                    Cells(eRow, 15).Value = ComboBox6.Text
                    Cells(eRow, 10).Value = ComboBox5.Text
                    Cells(eRow, 11).Value = TextBox7.Text
                    Cells(eRow, 12).Value = TextBox8.Text
                    Cells(eRow, 13).Value = TextBox6.Text
                    Cells(eRow, 14).Value = ComboBox2.Text
                    
                [COLOR=darkblue]Else[/COLOR]
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Title"
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

Hi,
I have try out the code, but if the value in textbox8 is less than 3, it will not able to store the data inside the excel sheet.
I wish that if the data which is less than 3, it will auto store the data inside Overall sheet.
 
Upvote 0
kimberly090 said:
Hi,
I have try out the code, but if the value in textbox8 is less than 3, it will not able to store the data inside the excel sheet.
I wish that if the data which is less than 3, it will auto store the data inside Overall sheet.
Click to expand...


Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    
    Sheets("Overall").Activate
    
    [color=darkblue]With[/color] Me
        [color=darkblue]If[/color] Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox2.Value) * Len(.ComboBox3.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) = 0 [color=darkblue]Then[/color]
            MsgBox "Please Complete All Fields Before Submit"
        [color=darkblue]Else[/color]
            
            [color=darkblue]If[/color] [color=darkblue]CSng[/color](.TextBox8.Text) > 3 [color=darkblue]Then[/color]
                [color=darkblue]If[/color] MsgBox("TextBox8 exceeds 3.0" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbNo [color=darkblue]Then[/color]
                          
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Title"
                    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
                 [color=darkblue]End[/color] [color=darkblue]If[/color]
             [color=darkblue]End[/color] [color=darkblue]If[/color]
                    
            eRow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = ComboBox1.Text
            Cells(eRow, 2).Value = TextBox2.Text
            Cells(eRow, 3).Value = TextBox3.Text
            Cells(eRow, 4).Value = TextBox1.Text
            Cells(eRow, 5).Value = ComboBox3.Text
            Cells(eRow, 6).Value = TextBox4.Text
            Cells(eRow, 7).Value = TextBox5.Text
            Cells(eRow, 8).Value = ComboBox4.Text
            Cells(eRow, 15).Value = ComboBox6.Text
            Cells(eRow, 10).Value = ComboBox5.Text
            Cells(eRow, 11).Value = TextBox7.Text
            Cells(eRow, 12).Value = TextBox8.Text
            Cells(eRow, 13).Value = TextBox6.Text
            Cells(eRow, 14).Value = ComboBox2.Text
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    
    Sheets("Overall").Activate
    
    [COLOR=darkblue]With[/COLOR] Me
        [COLOR=darkblue]If[/COLOR] Len(.ComboBox1.Value) * Len(.TextBox1.Value) * Len(.ComboBox2.Value) * Len(.ComboBox3.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) = 0 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Please Complete All Fields Before Submit"
        [COLOR=darkblue]Else[/COLOR]
            
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]CSng[/COLOR](.TextBox8.Text) > 3 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] MsgBox("TextBox8 exceeds 3.0" & vbLf & vbLf & _
                          "Do you wish to continue?", vbYesNo, "Exceeds") = vbNo [COLOR=darkblue]Then[/COLOR]
                          
                    MsgBox "user to re-type the value in TextBox8.", vbInformation, "Title"
                    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
                 [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
             [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                    
            eRow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Cells(eRow, 1).Value = ComboBox1.Text
            Cells(eRow, 2).Value = TextBox2.Text
            Cells(eRow, 3).Value = TextBox3.Text
            Cells(eRow, 4).Value = TextBox1.Text
            Cells(eRow, 5).Value = ComboBox3.Text
            Cells(eRow, 6).Value = TextBox4.Text
            Cells(eRow, 7).Value = TextBox5.Text
            Cells(eRow, 8).Value = ComboBox4.Text
            Cells(eRow, 15).Value = ComboBox6.Text
            Cells(eRow, 10).Value = ComboBox5.Text
            Cells(eRow, 11).Value = TextBox7.Text
            Cells(eRow, 12).Value = TextBox8.Text
            Cells(eRow, 13).Value = TextBox6.Text
            Cells(eRow, 14).Value = ComboBox2.Text
            
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

Hi Hi
thank for your kind reply, but I have another question is ,
I want to add additional pop out message box if :

  1. combobox2 value equals to Ni, the value in combobox6 need to be within 50T, 30U, 20A or 100, if combobox2 value equals to NiAu, the value in combobox6 need to be within 60s, 70A,80S or 80A
  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.

how should I add in ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Userform as search tool
Replies
5
Views
220
Akuini
Akuini
R
  • Question
Msg box for
Replies
1
Views
89
Micron
M
P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
182
Patriot2879
P
P
  • Solved
Code doesn't go on a new line (emptyRow)
Replies
8
Views
225
Fluff
Fluff
R
  • Solved
finding last row issue
Replies
3
Views
76
Fluff
Fluff

Forum statistics

Threads
1,214,583
Messages
6,120,377
Members
448,955
Latest member
BatCoder

Share this page

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