+ Reply to Thread
Results 1 to 2 of 2

Userform - stop user saving or closing form is not complete

Hybrid View

  1. #1
    Registered User
    Join Date
    02-14-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    14

    Userform - stop user saving or closing form is not complete

    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

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Userform - stop user saving or closing form is not complete

    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
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1