Userform - stop user saving or closing form is not complete

**denise6372** · 09-17-2012, 11:38 PM

Hi
I have a userform which contains a number of combo boxes and text boxes. At the bottom there are two buttons - Add Data and Close Form. At present users can press add data even if not all the boxes are complete I would like to stop this, and also for a message to pop up saying "Data Saved" when it is complete and they have clicked add data.

here is the code I have so far, as you can see ath present the form is only checking for the carer name:

Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Datastore")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a carer name
If Trim(Me.lbcarername.Value) = "" Then
Me.lbcarername.SetFocus
MsgBox "Please select your name"

End If
Exit Sub

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.lbcarername.Value
ws.Cells(iRow, 2).Value = Me.lbsitename.Value
ws.Cells(iRow, 3).Value = Me.tbdate.Value
ws.Cells(iRow, 4).Value = Me.tbpatientname.Value
ws.Cells(iRow, 5).Value = Me.cbtype.Value
ws.Cells(iRow, 6).Value = Me.lbservicegiven.Value
ws.Cells(iRow, 7).Value = Me.lbtimetaken.Value
ws.Cells(iRow, 8).Value = Me.lbcomplexity.Value
ws.Cells(iRow, 9).Value = Me.tbNoAttendees.Value

'clear the data
Me.lbcarername.Value = ""
Me.lbsitename.Value = ""
Me.tbdate.Value = ""
Me.cbtype.Value = ""
Me.tbpatientname.Value = ""
Me.lbservicegiven.Value = ""
Me.lbtimetaken.Value = ""
Me.lbcomplexity.Value = ""
Me.tbNoAttendees.Value = ""

Me.lbcarername.SetFocus

End Sub

**HaHoBe** · 09-18-2012, 12:24 AM

Hi, denise6372,

could you be kind enough as to add Code-Tags to your post? TIA

Instead of

'check for a carer name
If Trim(Me.lbcarername.Value) = "" Then
Me.lbcarername.SetFocus
MsgBox "Please select your name"


End If
Exit Sub

where the Exit Sub should be included in the If-Statement as otherwise the Sub would be quit in any case try something like this (untested):

'...
Dim ctrl As Object
'...
For Each ctrl In Me.Controls
  If Left(ctrl.Name, 2) = "lb" Then
    If ctrl.ListIndex = -1 Then
      ctrl.SetFocus
      MsgBox "Data missing in this control"
      Exit Sub
    End If
  ElseIf Left(ctrl.Name, 2) = "tb" Then
    If Trim(ctrl.Value) = "" Then
      ctrl.SetFocus
      MsgBox "Data missing in this control"
      Exit Sub
    End If
  End If
Next ctrl

Ciao,
Holger