stop writting to sheet after click on No on msgbox

DB73

DB73

Board Regular
Joined
Jun 7, 2022
Messages
102
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2010
  6. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
i'm trying to figure out to prevent writing to my sheet after vbNo is choosen
if i click No, it still writes to my sheet, but i want to return to my userform to ajust the values i filled in on my userform

any help would be greatfull...

VBA Code: 
Private Sub CommandButton5_Click()

    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes
        If vbYes Then
   
                            'Datum
                                 Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
                             'ziek/werkdag/feestdag
                                 Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
                             'project
                                 Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
                             'werk locatie
                                 Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
                             'normale of over uren
                                 Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
                             'uur tarief percentage
                                 Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
                             'starttijd
                                 Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
                             'eindtijd
                                 Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
                             'pauze
                                 Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
                             'BTW verlegd ja/nee
                                 Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
                             'BTW heffing
                                 Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
                             'Rit van
                                 Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
                             'Rit naar
                                 Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
                             'Enkele reis of retour rit
                                 Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
                             'rede rit
                                 Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
                             'declaratie bedrag pkm zakelijk
                                 Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
                             'overige declaratie
                                 Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
                             'reden overige declaratie
                                 Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
                        Exit Sub
                    End If
                answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes
             If vbYes Then
        MyForm.Show
    End If
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Change this:
VBA Code: 
    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes

to this:
VBA Code: 
    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo)

(in two places).
 
Upvote 0
DB73 said:
i'm trying to figure out to prevent writing to my sheet after vbNo is choosen
if i click No, it still writes to my sheet, but i want to return to my userform to ajust the values i filled in on my userform

any help would be greatfull...

VBA Code: 
Private Sub CommandButton5_Click()

    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes
        If vbYes Then
  
                            'Datum
                                 Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
                             'ziek/werkdag/feestdag
                                 Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
                             'project
                                 Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
                             'werk locatie
                                 Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
                             'normale of over uren
                                 Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
                             'uur tarief percentage
                                 Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
                             'starttijd
                                 Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
                             'eindtijd
                                 Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
                             'pauze
                                 Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
                             'BTW verlegd ja/nee
                                 Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
                             'BTW heffing
                                 Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
                             'Rit van
                                 Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
                             'Rit naar
                                 Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
                             'Enkele reis of retour rit
                                 Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
                             'rede rit
                                 Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
                             'declaratie bedrag pkm zakelijk
                                 Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
                             'overige declaratie
                                 Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
                             'reden overige declaratie
                                 Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
                        Exit Sub
                    End If
                answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes
             If vbYes Then
        MyForm.Show
    End If
End Sub
Click to expand...
i have the cmdbutton5 to open a msgbox. (not another userform)
is asks; everything correct?
when click on No, it has t return to the userform (so i can correct the things over there)
when i click Yes it has to write the values to my sheet and it has to open another msgbox with question;
add more items (then it has to reopen the userform or clear the values so i can add new in
when i click no it has to close the msgbox and the userform
 
Upvote 0
rlv01 said:
Change this:
VBA Code: 
    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo) = vbYes

to this:
VBA Code: 
    answer = MsgBox("Alles juis ingevuld", vbQuestion + vbYesNo)

(in two places).
Click to expand...
thats not working🥺...after i click on No at the firt msgbox it still writes to my sheet.
i want to return at No whit no writing
 
Upvote 0
DB73 said:
thats not working🥺...after i click on No at the firt msgbox it still writes to my sheet.
i want to return at No whit no writing
Click to expand...

It works for me, so please post your modified code for Sub CommandButton5_Click() so I can see how you made the changes I recommended.
 
Upvote 0
rlv01 said:
It works for me, so please post your modified code for Sub CommandButton5_Click() so I can see how you made the changes I recommended.
Click to expand...
VBA Code: 
Private Sub CommandButton5_Click()

    answer = MsgBox("Alles juist ingevuld", vbQuestion + vbYesNo)
        If vbYes Then
   
                            'Datum
                                 Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
                             'ziek/werkdag/feestdag
                                 Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
                             'project
                                 Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
                             'werk locatie
                                 Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
                             'normale of over uren
                                 Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
                             'uur tarief percentage
                                 Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
                             'starttijd
                                 Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
                             'eindtijd
                                 Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
                             'pauze
                                 Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
                             'BTW verlegd ja/nee
                                 Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
                             'BTW heffing
                                 Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
                             'Rit van
                                 Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
                             'Rit naar
                                 Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
                             'Enkele reis of retour rit
                                 Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
                             'rede rit
                                 Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
                             'declaratie bedrag pkm zakelijk
                                 Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
                             'overige declaratie
                                 Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
                             'reden overige declaratie
                                 Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
                            Unload Me
                        Exit Sub
                    End If
                answer = MsgBox("Alles juist ingevuld", vbQuestion + vbYesNo)
             If vbNo Then
        MyForm.Show
    End If
End Sub

i add "unload me" at the vbYes
but at vbNo it stille writs to my sheet
 
Upvote 0
One more thing I changed, but forgot to post. Change this:
VBA Code: 
   If vbYes Then

to this:
VBA Code: 
   If answer = vbYes Then

(also two places)
 
Upvote 0
VBA Code: 
'Test version
Private Sub CommandButton5_Click()
    Dim answer As Integer

    answer = MsgBox("Alles juist ingevuld", vbQuestion + vbYesNo)

    Select Case answer
    Case vbYes

        'Datum
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
        'ziek/werkdag/feestdag
        Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
        'project
        Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
        'werk locatie
        Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
        'normale of over uren
        Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
        'uur tarief percentage
        Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
        'starttijd
        Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
        'eindtijd
        Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
        'pauze
        Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
        'BTW verlegd ja/nee
        Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
        'BTW heffing
        Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
        'Rit van
        Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
        'Rit naar
        Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
        'Enkele reis of retour rit
        Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
        'rede rit
        Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
        'declaratie bedrag pkm zakelijk
        Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
        'overige declaratie
        Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
        'reden overige declaratie
        Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
        Unload Me
        Exit Sub
    Case vbNo
        MyForm.Show
    End Select
End Sub
 
Upvote 0
rlv01 said:
VBA Code: 
'Test version
Private Sub CommandButton5_Click()
    Dim answer As Integer

    answer = MsgBox("Alles juist ingevuld", vbQuestion + vbYesNo)

    Select Case answer
    Case vbYes

        'Datum
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
        'ziek/werkdag/feestdag
        Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
        'project
        Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
        'werk locatie
        Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
        'normale of over uren
        Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
        'uur tarief percentage
        Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
        'starttijd
        Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
        'eindtijd
        Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
        'pauze
        Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
        'BTW verlegd ja/nee
        Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
        'BTW heffing
        Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
        'Rit van
        Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
        'Rit naar
        Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
        'Enkele reis of retour rit
        Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
        'rede rit
        Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
        'declaratie bedrag pkm zakelijk
        Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
        'overige declaratie
        Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
        'reden overige declaratie
        Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
        Unload Me
        Exit Sub
    Case vbNo
        MyForm.Show
    End Select
End Sub
Click to expand...
this 1 gives a fault on 'Myform.show"after clicking on No:unsure:
 
Upvote 0
got it working...
now i need somthing to find when a box is empty and i click on yes on a empty userform

working code;
VBA Code: 
 Private Sub CommandButton5_Click()

    answer = MsgBox("Alles juist ingevuld", vbQuestion + vbYesNo)
        If answer = vbYes Then
   
                                'Datum
                                     Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = CDate(TheDate.Value)
                                 'ziek/werkdag/feestdag
                                     Range("M" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox1.Text
                                 'project
                                     Range("N" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox2.Text
                                 'werk locatie
                                     Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox3.Text
                                 'normale of over uren
                                     Range("R" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox4.Text
                                 'uur tarief percentage
                                     Range("S" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox5.Text
                                 'starttijd
                                     Range("T" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox6.Text
                                 'eindtijd
                                     Range("U" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox7.Text
                                 'pauze
                                     Range("V" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox8.Text
                                 'BTW verlegd ja/nee
                                     Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox9.Text
                                 'BTW heffing
                                     Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox10.Text
                                 'Rit van
                                     Range("AD" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox11.Text
                                 'Rit naar
                                     Range("AH" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox12.Text
                                 'Enkele reis of retour rit
                                     Range("AL" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox13.Text
                                 'rede rit
                                     Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox14.Text
                                 'declaratie bedrag pkm zakelijk
                                     Range("AO" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox15.Text
                                 'overige declaratie
                                     Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox16.Text
                                 'reden overige declaratie
                                     Range("AS" & Rows.Count).End(xlUp).Offset(0, 0).Value = ComboBox17.Text
                                Unload Me
                        answer = MsgBox("meer items toevoegen", vbQuestion + vbYesNo)
                    End If
                 If answer = vbYes Then
           MyForm.Show
        End If
    Exit Sub
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
382
sspatriots
S
S
  • Question
Worksheet Change Event
Replies
2
Views
387
sspatriots
S
S
  • 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
348
sspatriots
S
E
  • Question
Excel VBA to Outlook Email Draft - How to save email draft copy to sharepoint / How to use template from sharepoint?
Replies
0
Views
306
elitef
E
K
  • Solved
fix error 1004 when the sheet is protected
Replies
1
Views
327
dave3009
dave3009

Forum statistics

Threads
1,215,219
Messages
6,123,684
Members
449,116
Latest member
HypnoFant

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