Detect option button, exit, recheck, proceed

[edge1282]

Hi - new to forum. I have a userform which requires one of 5 option buttons to be selected. After pressing the command button in the user form, i want to check that an option button has been selected. if it hasn't a message should appear to warn user that they need to select a button. After clicking on OK in the message box, I want the user to be able to continue with the form and select an option button. Alternatively, I want the userform to close at that point and no further code is executed.

The code I have at present warns the user, but then continues to the end of the macro, posting incomplete info to the spreadsheet.

Start of code is as follows:

Private Sub CommandButton1_Click()
' check that an insurance company has been selected
     Dim cnt As Integer
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = True Then cnt = cnt + 1
            
        End If
     Next ctl
     If cnt = 0 Then MsgBox "You must select an insurance company!"

I have tried adding Exit Sub as per below. This returns me to the form, but even after selecting an option button I cannot continue.

Private Sub CommandButton1_Click()
' check that an insurance company has been selected
     Dim cnt As Integer
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = True Then cnt = cnt + 1
            
        End If
     Next ctl
     If cnt = 0 Then MsgBox "You must select an insurance company!"
     Exit Sub

Thanks in advance.

 

[My Aswer Is This]

I do not believe what your asking for is possible.

 

[Fluff]

Try

     If cnt = 0 Then
       MsgBox "You must select an insurance company!"
       Exit Sub
     End If

 

[edge1282]

Thanks Fluff. I tried that but it gave an error. I've added in some workaround code that allows the incomplete data to be added to the table but then looks for the incomplete row and deletes it. This seems to be working fine.

 

[Fluff]

Glad you got it sorted & thanks for the feedback