VBA - Go back to Userform after msgbox

Question

I have a userform that currently has 15 comboboxes in it. Allowing the user to select 5 reports for this quarter, last quarter and last year. As this is quite a lot of boxes to select from, I thought it would be nice to apply some error handling to avoid any boxes being missed.

The code I have is this (obviously repeated for each box):

If IsNull(UF_RM_Reports.Report1.Value) Then

MsgBoxResult = MsgBox("No report selected for Report 1 current quarter, is this correct?", vbYesNo + vbQuestion, "Report Template")

    If MsgBoxResult = vbNo Then
        End

    Else

    End If

End If

The problem I'm having, is if the user realises they haven't selected a report and presses No, I have end which doesn't just end the macro but also closes the combobox. If you then reopen it all selection are gone. Kind of defeating the point of the error handling, as they need to start again.

I was wondering if there is anything I could change End from that stops the code but allows the user to go back and select the missing report.

Thanks in advance

Answer 1

There are several ways to handle this situation. Since I don't know the details of your UserForm I will outline general approaches. I would be inclined to validate the controls upon pressing the button on the UserForm which signals the user is ready to generate the reports:

Private Sub CommandButton1_Click()
   If Trim(Report1.Value) = "" Then
      If MsgBox("No report selected for Report 1...", vbYesNo + vbQuestion, "Report Template") = vbNo Then
         Report1.SetFocus
         Exit Sub
      End If
   End If

   If Trim(Report2.Value) = "" Then
      If MsgBox("No report selected for Report 2...", vbYesNo + vbQuestion, "Report Template") = vbNo Then
         Report2.SetFocus
         Exit Sub
      End If
   End If

   'create the reports when nothing has been missed or
   'the user wants to proceed anyway
   MsgBox "create reports"
End Sub

Since there are a lot of controls, you could accomplish the same thing with a loop:

Private Sub CommandButton1_Click()
   Dim c As Control
   
   For Each c In Me.Controls
      If TypeOf c Is ComboBox Then
         If Trim(c.Value) = "" Then
            If MsgBox("You have not selected all reports.  Is this correct?", vbYesNo + vbQuestion, "Report Template") = vbNo Then
               Report1.SetFocus
               Exit Sub
            Else
               Exit For
            End If
         End If
      End If
   Next
   
   'create the reports when nothing has been missed or
   'the user wants to proceed anyway
   MsgBox "create reports"
End Sub