User form not to throw a Message Box if is not completely filled out

[sdoppke]

Hi everyone, i have a user form that uses all combo box (drop-downs). Can some help me to modify this code to throw up a message box saying "finish form" before it transfers the data (unload me)?

Thanks a ton sd

Private Sub CommandButton1_Click()
     
    Dim ws As Worksheet, rngFind, rngRow As Range
     
    Set ws = ThisWorkbook.Sheets("StoreHours")
    StoreNumber = Worksheets("MyStoreInfo").Range("C2")
     
    Set rngFind = ws.Range("C:C").Find(what:=StoreNumber, MatchCase:=True)
     
    If Not rngFind Is Nothing Then
         
    rngFind.Offset(0, 3).Value = Me.SundayOpen.Value
    rngFind.Offset(0, 4).Value = Me.SundayClose.Value
    rngFind.Offset(0, 5).Value = Me.MondayOpen.Value
    rngFind.Offset(0, 6).Value = Me.MondayClose.Value
    rngFind.Offset(0, 7).Value = Me.TuesdayOpen.Value
    rngFind.Offset(0, 8).Value = Me.TuesdayClose.Value
    rngFind.Offset(0, 9).Value = Me.WednesdayOpen.Value
    rngFind.Offset(0, 10).Value = Me.WednesdayClose.Value
    rngFind.Offset(0, 11).Value = Me.ThursdayOpen.Value
    rngFind.Offset(0, 12).Value = Me.ThursdayClose.Value
    rngFind.Offset(0, 13).Value = Me.FridayOpen.Value
    rngFind.Offset(0, 14).Value = Me.FridayClose.Value
    rngFind.Offset(0, 15).Value = Me.SaturdayOpen.Value
    rngFind.Offset(0, 16).Value = Me.SaturdayClose.Value
    rngFind.Offset(0, 17).Value = Me.Month.Value
    rngFind.Offset(0, 18).Value = Me.Year.Value
    MsgBox "Store Hours have been updated)", vbInformation, "Update Store Hours"
    Unload Me
       
    Else
         MsgBox "Your Store Number is not input on the MY STORE INFO Tab", vbInformation, "Update Store Hours"
       
    End If
        
End Sub

 

[Nalani]

Assuming your ComboBoxes are named as you have them, i.e.

SundayOpen
SundayClose
MondayOpen
MondayClose
etc.

Try this: Not Tested

 '//Check for all required entries
         If SundayOpen = "" Or SundayClose = "" _
       Or MondayOpen = "" Or MondayClose = "" Then  ' //add more (all) OR statements if needed
 
        '//Notify user if entries are missing
        MsgBox prompt:="All fields are required", _
               Title:="REMINDER"
        End If
    Exit Sub

 

[mikerickson]

Private Sub CommandButton1_Click()
    If Not (AllComboBoxesFilled()) Then
        MsgBox "Fill out all the comboboxes"
    Else
        Rem do stuff
    End If
End Sub

Function AllComboBoxesFilled() As Boolean
    Dim oneCB As Object

    AllComboBoxesFilled = True

    For Each oneCB In Me.Controls
        If TypeName(oneCB) = "ComboBox" Then
            AllComboBoxesFilled = AllComboBoxesFilled And (oneCB.Text <> vbNullString)
        End If
    Next oneCB
End Function

 

[sdoppke]

Private Sub CommandButton1_Click()
    If Not (AllComboBoxesFilled()) Then
        MsgBox "Fill out all the comboboxes"
    Else
        Rem do stuff
    End If
End Sub
 
Function AllComboBoxesFilled() As Boolean
    Dim oneCB As Object
 
    AllComboBoxesFilled = True
 
    For Each oneCB In Me.Controls
        If TypeName(oneCB) = "ComboBox" Then
            AllComboBoxesFilled = AllComboBoxesFilled And (oneCB.Text <> vbNullString)
        End If
    Next oneCB
End Function

This works great!! I really appreciate your help.

One more ask, is there a way to disable the close button on the user form? Im trying to get rid of any way for someone get past the form without completing it, except the cancel button.

sd

 

[mikerickson]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = (1 - CloseMode)
    If Cancel Then
        MsgBox "Press button to exit"
    End If
End Sub

 

[sdoppke]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = (1 - CloseMode)
    If Cancel Then
        MsgBox "Press button to exit"
    End If
End Sub

Wonderfull! I so appreciate it, does exactly what i needed.

sd