Thread: after msgbox go to not answered groupbox (radiobutons) before continu with next macro

Hi there,

I'm new here and not very familiair with VBA.
I've searched on the internet for many days but can't find to solve my problem.

I've an Excel Workbook with several sheets.
A sheet named "Vragenlijst" contains 20 questions. Each question has 4 option buttons (Control Toolbox) in a group.

In the second sheet named "Controlevelden" the values of the 20 aswers are registered in colom B2 to B21.
In B27 i wrote a formula: =COUNT.IF(B2:B21;">0")>=20

After the questions are aswered there will be a summary of textual answers on the sheet named "Rapportage".

I have 1 command button proceeding all the actions neccesary. After pushing the button
a macro takes a picture (with 'camera'-option in Excel) of this summary and places it on a special place on sheet "Vragenlijst"

A MsgBox is giving me a box with "You didn't anwer 1 (or more) questions" when a question has not been answers (option button not chosen). When all the question are answered: "You answered all the question".

The problem is that after "You didn't asnwer 1 (or more) questions" it continues with the next macro in VBA.
I want to go to the not answered question before going further with the VBA command.

The command button has this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = "A1:F132"
End Sub


Private Sub ComboBox1_Change()

End Sub

Private Sub commandbutton1_click()

checkvragen
foto
foto2
Macro2
Mail_workbook_Outlook_1
End Sub

the module (sub) 'checkvragen' has this code:

Option Explicit

Sub checkvragen()
    Dim rRange As Range
    Dim oCell As Variant
    Dim Counter As Long
    Dim Cancel As Integer
    Counter = 0
    Set rRange = Sheets("Controlevelden").Range("B2:B21")
    For Each oCell In rRange
        If oCell.Value = 0 Then
            Counter = Counter + 1
        End If
    Next
    If Counter <> 0 Then
        MsgBox "U heeft  " & Counter & "  vraag/vragen nog niet beantwoord."
        Else: MsgBox "U heeft alle vragen beantwoord."
        Cancel = True
        End If
End Sub

Can anyone help me please?

hi VBAStarter

Option Explicit
dim AdvanceStuff as boolean

Private Sub commandbutton1_click()
checkvragen
If AdvanceStuff then 
foto
foto2
Macro2
Mail_workbook_Outlook_1
end if
End Sub

Sub checkvragen()
    Dim rRange As Range
    Dim oCell As Variant
    Dim Counter As Long
    Dim Cancel As Integer
    Counter = 0
    Set rRange = Sheets("Controlevelden").Range("B2:B21")
    For Each oCell In rRange
        If oCell.Value = 0 Then
            Counter = Counter + 1
        End If
    Next
    If Counter <> 0 Then
        MsgBox "U heeft  " & Counter & "  vraag/vragen nog niet beantwoord."
        AdvanceStuff= true
Else: MsgBox "U heeft alle vragen beantwoord."
        AdvanceStuff= false
        End If
End Sub

can you attach the workbook?

Hi Pike,

here is the workbook you were asking for.

Greetz,
VBA Starter

have i got the true and false around the wrong way?
try....

Option Explicit
Dim AdvanceStuff As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = "A1:F133"
End Sub

Private Sub commandbutton1_click()
checkvragen
Debug.Print AdvanceStuff
If AdvanceStuff Then
foto
foto2
Macro2
Mail_workbook_Outlook_1
End If
End Sub

Sub checkvragen()
    Dim rRange As Range
    Dim oCell As Variant
    Dim Counter As Long
    Dim Cancel As Integer
    Counter = 0
    Set rRange = Sheets("Controlevelden").Range("B2:B21")
    For Each oCell In rRange
    Debug.Print oCell.Value
        If oCell.Value = 0 Then
            Counter = Counter + 1
        End If
    Next
    If Counter <> 0 Then
        MsgBox "U heeft  " & Counter & "  vraag/vragen nog niet beantwoord."
        AdvanceStuff = True
       Else: MsgBox "U heeft alle vragen beantwoord."
        AdvanceStuff = False
        End If
End Sub

extra information: in VBA 'ActiveSheet.Pictures.Paste.Select' is highlighted in yellow, so this action won't take place.

Hi
You will need to unprotect the sheet before you run the macro then protect after it has run

Hello Pike,

this method didn't work for me. Only when i selected 'Edit Objects' in sheetprotection, this would work. But then all the other object were unprotected.

I found out on the internet the following code

 ActiveSheet.Protect Password:="MyPassword", UserInterfaceOnly:=True

. This works fine for me. I placed it in a module and add it to the commandbutton.

Thanks for all the good ideas!