hide modeless userform if range is nothing

Question

This macro searches a range for cells containing errors, if a cell with error is found the userform allows you to change that cell to "Yes", "no", or "review later" via 3 different command buttons. If there are no erros found (ie. CheckRange is nothing) a msgbox pops up to let you know that and then the userform should hide.

Problem: The whole macro works perfectly, except I can not get the userform to hide. The msg box even comes up as planned when no errors are found. But the userform is still there.

Sub UserformYes_no_review()
Dim Custchk As CustomListCheck
Set Custchk = VBA.UserForms.Add(CustomListCheck.Name)
Set CheckRange = Nothing

With New CustomListCheck
    On Error Resume Next
    Set CheckRange = Sheets("Sheet1").Range("A1:N2000").SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0

    If CheckRange Is Nothing Then
      MsgBox "All items have been accounted for"
      CustomListCheck.Hide
      Exit Sub
    Else
        For Each Cell In CheckRange
            Cell.Select
            If VarType(ActiveCell.Value) = vbError Then
                Custchk.Show vbModeless
            End If

        Next Cell
    End If
End With

End Sub



Private Sub CommandButton1_Click()
ActiveCell.Value = "Yes"    
Call UserformYes_no     
End Sub

Private Sub CommandButton2_Click()
ActiveCell.Value = "No"
Call UserformYes_no   
End Sub

Private Sub CommandButton3_Click()
ActiveCell.Value = "Review Later"
Call UserformYes_no
End Sub

When you show a modeless userform your VBA doesn't wait for the form to close, so you probably don't want to do that in a loop. — Tim Williams
Thanks Tim, unfortunately this is the only way I could formulate this to my needs. I have never worked with modeless userforms so I appreciate the insight as it led me to a solution. — Ryan S

Ryan S Ryan S · Accepted Answer · 2019-12-19T22:03:35

Got around this by adding unload me to each command button private sub as seen below

Private Sub CommandButton1_Click()
ActiveCell.Value = "Yes"
Unload Me
Call UserformYes_no


End Sub

hide modeless userform if range is nothing

1 Answers