Prompt for unselected option button

[TerenceTitus]

Hi,

I am facing some trouble formulating a macro to run upon closure of workbook. I would like to run a macro to check if there are unselected option buttons within my specific sheet. The option buttons grouped in rows of 5 where only 1 can be selected per row and it runs down as long as the survey continues.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

For Each vctrl In ActiveWorkbook.Worksheets("Survey").Shapes
    If vctrl.Type = msoFormControl Then
        If vctrl.FormControlType = xlOptionButton Then
            If vctrl.DrawingObject.Value = False Then MsgBox Prompt:="You have not answered all questions!"
                If MsgBox("Do you still want to close the survey?", vbYesNo) = vbNo Then Exit Sub
        End If
    End If
    Next
End Sub

It does not prompt me on unselected option buttons and I cant make the macro end if the user selects do not close survey.

 

[Jaafar Tribak]

Are you grouping each group of option buttons inside a frame ?

 

[TerenceTitus]

Hi Jaafar,

Yes they are being grouped inside a frame, I used form control.

 

[Jaafar Tribak]

Hi Jaafar,

Yes they are being grouped inside a frame, I used form control.

So that means you will need to have one option button selected in each frame before closing - Am I correct ?

 

[Jaafar Tribak]

Try this and see if it works for you :

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim vctrl As Shape, iFramesCount As Integer, iSelectdOptionButtonsCount As Integer
    
    For Each vctrl In ActiveWorkbook.Worksheets("Survey").Shapes
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlGroupBox Then
                iFramesCount = iFramesCount + 1
            End If
        End If
    
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlOptionButton Then
                If vctrl.OLEFormat.Object.Value = 1 Then
                    iSelectdOptionButtonsCount = iSelectdOptionButtonsCount + 1
                End If
            End If
        End If
    Next
    
    If iFramesCount > iSelectdOptionButtonsCount Then
        MsgBox Prompt:="You have not answered all questions!"
        If MsgBox("Do you still want to close the survey?", vbYesNo) = vbNo Then Cancel = True
    End If

End Sub

 

[TerenceTitus]

Dear Jaafar,

Many thanks for your help, it works! I didnt know how to use the frame as indicated in your code, i only knew that the command had to recognise the grouping somehow.

Sorry to ask again, i have another problem that i linked to the one you just solved. I have a command button ("Clear Data") for users to remove their responses (unselect the grouped option buttons). It worked in my previous version when i didnt group the buttons by rows, they were as individuals. I tried combining what you provided me about the groupbox but im still not successful.

Private Sub Clear_Click()

'message bos to confirm deletion
If MsgBox("This will delete your responses! Are you sure?", vbYesNo) = vbNo Then Exit Sub


Dim vctrl As Shape, iFramesCount As Integer, iSelectdOptionButtonsCount As Integer
    
    For Each vctrl In ActiveWorkbook.Worksheets("Survey").Shapes
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlGroupBox Then
                iFramesCount = iFramesCount + 1
            End If
        End If
    
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlOptionButton Then
                vctrl.OLEFormat.Object.Value = False
            End If
        End If
    Next
    
    'create a message box
MsgBox Prompt:="Your responses has been deleted"


End Sub

 

[Jaafar Tribak]

Upu could put this Macro in the ThisWorkbook Module and assign it to your ("Clear Data") Button

Public Sub ClearOptionButtons()

    Dim vctrl As Shape

    For Each vctrl In ActiveWorkbook.Worksheets("Survey").Shapes
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlOptionButton Then
            vctrl.OLEFormat.Object.Value = 0
            End If
        End If
    Next
End Sub

 

[Rick Rothstein]

Are you trying to clear all of the Form Control OptionButtons on the worksheet (no matter what Frame they are in)? If so, this single line of code should work...

Sheets("Survey").OptionsButtons = False

 

[TerenceTitus]

Hi Both Jaafar and Rick,

Sorry for the late reply. I have tried both ways that you suggested, sadly it did not work. It works for option boxes that are not grouped together. I'm not too sure as to why it does not work.

I am currently using a group box form control for each row, 5 options boxes each.