VBA - Go back to Userform after msgbox-CodePudding

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

CodePudding user response：

If the requirement is to bring the user's attention to the fact that he/she missed a report selection, why not try fiddling with the color property of combobox in any one of it's events - something like ?

Set the color state of combobox at the useform activate event

Private Sub UserForm_Activate()

  ComboBox1.BackColor = vbRed
  UserForm1.ComboBox1.AddItem "A"
  UserForm1.ComboBox1.AddItem "B"
  UserForm1.ComboBox1.AddItem "C"

End Sub

Then change the color back to original in the combobox change event

Private Sub ComboBox1_Change()
     If ComboBox1.value = "" Then
        ComboBox1.BackColor = vbRed
     Else
       ComboBox1.BackColor = &H80000005
     End If
End Sub

CodePudding user response：

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