Pop-up Box when closing Userform that shows all the information you entered.

B

Botje

New Member
Joined
Aug 23, 2011
Messages
48
Hey,

After googling and searching this forum this is my last resort again.
All i can find are the "pop-up boxes" that i've already got.

What i am trying to achieve is the following:

I got a userform with 20 check boxes; 1 combobox and 2 txtbox.

I want a box to pop up when closing the form that shows all the information i entered.
Want it to show the following:

#1 Which boxes i've check.
#2 What choice i made in the combobox
#3 What information i filled into the textbox.

I do have boxes that pop-up if the information isn't filled in, and i am trying to achieve a "similair layout".
Code: 
   If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If

Thanks in advance.

This is the entire code i use for the form.
Code: 
Private Sub cmdinvullen_Click()
Dim RowCount As Long
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
 If Me.ComboBox1.Value = "" Then
    MsgBox "Vul alsjeblieft je team in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.ComboBox1.SetFocus
    Exit Sub
End If
If MsgBox("Is alles ingevuld?", vbYesNo) = vbYes Then
MsgBox "Proficiat"
End If
RowCount = Worksheets("Archief").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 2).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 30).Value = "Tijd"
Else
.Offset(RowCount, 30).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 31).Value = "Geld"
Else
.Offset(RowCount, 31).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 32).Value = "Collega's"
Else
.Offset(RowCount, 32).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 33).Value = "Toestemming"
Else
.Offset(RowCount, 33).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 34).Value = "Ruimte"
Else
.Offset(RowCount, 34).Value = ""
End If
If Me.chkAnders.Value = True Then
.Offset(RowCount, 35).Value = "Anders"
Else
.Offset(RowCount, 36).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 40).Value = "Achmea Vitale"
Else
.Offset(RowCount, 40).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 41).Value = "Keuringen"
Else
.Offset(RowCount, 41).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 42).Value = "Klantenservice"
Else
.Offset(RowCount, 42).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 43).Value = "Frontoffice"
Else
.Offset(RowCount, 43).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 44).Value = "Exoten"
Else
.Offset(RowCount, 44).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 45).Value = "MO MKB"
Else
.Offset(RowCount, 45).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 46).Value = "DAM"
Else
.Offset(RowCount, 46).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 47).Value = "Bedrijfsartsen en Consulenten"
Else
.Offset(RowCount, 47).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 48).Value = "RAM"
Else
.Offset(RowCount, 48).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 49).Value = "SAM"
Else
.Offset(RowCount, 49).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 50).Value = "MO AMI"
Else
.Offset(RowCount, 50).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 51).Value = "MO GM AT5"
Else
.Offset(RowCount, 51).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 52).Value = "Interventie Coördinatoren"
Else
.Offset(RowCount, 52).Value = ""
End If
.Offset(RowCount, 3).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub
 
Last edited:
Sort by date Sort by votes
Norie said:
How are you trying to combine them?

I suppose the prompt to review could be in the click event of the button but I'm not sure how you would implement it.

What is it you actually want to happen and in what order?

I'm actually not sure using the QueryClose is needed since you don't actually do anything else in it, eg transfer the data to the worksheet.
Click to expand...


What i want to happen(is happening) is the next:

If you fill in the first code. A box will pop-up when you are trying to send in the form.
Its shows "is this the information you filled in?"
Then you can hit the "Ok" Button. But there is no "Cancel Button"

Do you understand what i mean?

Trying to make this a Confirmation/Cancel Box.
So when you made for example a typo.
You can hit the Cancel button and retyp the information you want.

Its an "Are you sure this information is correct" Box


Code: 
Private Sub cmdinvullen_Click()
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
    If Me.ComboBox1.Value = "" Then
    MsgBox "Vul alsjeblieft je team in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.ComboBox1.SetFocus
    Exit Sub
End If
    If txtDatum.Value = "" Then
    MsgBox "Vul alsjeblieft een datum in."
    Exit Sub
Else
    If Not IsDate(txtDatum.Value) Then
    MsgBox "Ongeldige datum ingevoerd."
    Exit Sub
End If
End If
If MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit? " & vbNewLine & "Naam  :        " & Me.TxtNaam.Value & vbNewLine & "Team  :        " & Me.ComboBox1.Value & vbNewLine & "Idee    :        " & Me.txtidee.Value) = vbYesNo Then
MsgBox "Idee ingevoerd!"
End If
RowCount = Worksheets("Archief").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 14).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
.Offset(RowCount, 15).Value = Me.ComboBox1.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 19).Value = "X"
Else
.Offset(RowCount, 19).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 20).Value = "X"
Else
.Offset(RowCount, 20).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 21).Value = "X"
Else
.Offset(RowCount, 21).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 22).Value = "X"
Else
.Offset(RowCount, 22).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 23).Value = "X"
Else
.Offset(RowCount, 23).Value = ""
End If
If Me.chkAnders.Value = True Then
.Offset(RowCount, 24).Value = "X"
Else
.Offset(RowCount, 24).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 1).Value = "X"
Else
.Offset(RowCount, 1).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 2).Value = "X"
Else
.Offset(RowCount, 2).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 3).Value = "X"
Else
.Offset(RowCount, 3).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 4).Value = "X"
Else
.Offset(RowCount, 4).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 5).Value = "X"
Else
.Offset(RowCount, 5).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 6).Value = "X"
Else
.Offset(RowCount, 6).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 7).Value = "X"
Else
.Offset(RowCount, 7).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 8).Value = "X"
Else
.Offset(RowCount, 8).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 9).Value = "X"
Else
.Offset(RowCount, 9).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 10).Value = "X"
Else
.Offset(RowCount, 10).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 11).Value = "X"
Else
.Offset(RowCount, 11).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 12).Value = "X"
Else
.Offset(RowCount, 12).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 13).Value = "X"
Else
.Offset(RowCount, 13).Value = ""
.Offset(RowCount, 16).Value = Format(Now, "dd/mm/yyyy hh:nn")
End If
.Offset(RowCount, 17).Value = DateValue(txtDatum.Text)
End With
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
So don't need the prompt for the user to check the data before they close the form?

Do you actually want to check the first few entries, eg name, idea, etc, and then

If you do want a yes/no use vbYesNo:
Code: 
Dim Resp As Variant
 
   Resp = MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit?", vbYesNo +vbQuestion)
   
    If Resp = vbNo Then
           Exit Sub
    Else
           Msgbox "Idee ingevoerd!"
    End If
Fill in the rest of the message for the combobox yourself just as it is in your code.
 
Upvote 0
Norie said:
So don't need the prompt for the user to check the data before they close the form?

Do you actually want to check the first few entries, eg name, idea, etc, and then

If you do want a yes/no use vbYesNo:
Code: 
Dim Resp As Variant
 
   Resp = MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit?", vbYesNo +vbQuestion)
 
    If Resp = vbNo Then
           Exit Sub
    Else
           Msgbox "Idee ingevoerd!"
    End If
Fill in the rest of the message for the combobox yourself just as it is in your code.
Click to expand...


Thnx alot it works!

But.. it closes the sub instead of going back to the Form.

Code: 
End If
Dim Resp As Variant
    If MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit? " & vbNewLine & "Naam  :        " & Me.TxtNaam.Value & vbNewLine & "Team  :        " & Me.ComboBox1.Value & vbNewLine & "Idee    :        " & Me.txtidee.Value, vbYesNo + vbQuestion) Then
    If Resp = vbNo Then Return
    
    Else
           MsgBox "Idee ingevoerd!"

I could use the .Refresh but then i lose all the information.
I tried the "Return" but then it returns to VBA-editor.
Also tried:
-Then GoTo "name of the form"
-"name of the form".show
- Sub.intialize

Which "Sub" should i use?
 
Upvote 0
If the user clicks No in the messagebox the code I posted should exit the Click sub and return to the form with the data intact.

Isn't that what you want it to do?

If you do want to clear the form don't call the Initalize event, use something like this:
Code: 
Unload Me ' unload the form
 
UserForm1.Show ' show form again
Showing the form again will actually execute the Initialize event.

By the way, unless you are using Access, there are no Requery or Refresh methods.

PS Don't know why you tried Return, that's only used with GoSub and you definitely don't want to use GoSub.
 
Upvote 0
Norie said:
If the user clicks No in the messagebox the code I posted should exit the Click sub and return to the form with the data intact.

Isn't that what you want it to do?

If you do want to clear the form don't call the Initalize event, use something like this:
Code: 
Unload Me ' unload the form
 
UserForm1.Show ' show form again
Showing the form again will actually execute the Initialize event.

By the way, unless you are using Access, there are no Requery or Refresh methods.

PS Don't know why you tried Return, that's only used with GoSub and you definitely don't want to use GoSub.
Click to expand...


Thanks again for the information. I made a mistake by adding "then" after
Code: 
Resp = MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit? " & vbNewLine & "Naam  :        " & Me.TxtNaam.Value & vbNewLine & "Team  :        " & Me.ComboBox1.Value & vbNewLine & "Idee    :        " & Me.txtidee.Value, vbYesNo + vbQuestion)


It works great now!

Thanks for all the help and effort you put in this! :-D
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
79
6StringJazzer
6StringJazzer
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S
M
  • Solved
Show message box on userform after sum or subtraction
Replies
2
Views
105
Maklil
M
J
  • Question Question
User form successfully ran but no results are showing
Replies
1
Views
300
rlv01
R
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
494
Aurum1
A

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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