+ Reply to Thread
Results 1 to 2 of 2

VBA User Form Check all Fields Filled Combo box

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    27

    VBA User Form Check all Fields Filled Combo box

    Hi

    I have a user form which displays a message asking if the user wants to continue (yes/no) before writing the data to a tab, I have 2 questions:

    1. How can I ensure the data entered into the combo box is only selected from the drop down list (currently a user could type anything into this box and the user form would still work)?

    2. How can I ensure that the yes/no continue message only pops up when all fields are filled? Currently, even though I have a check that the fields are filled, the yes/no box will still pop up (see code below)

    Thanks.

    '*****************************************************************
    Private Sub cmdOK_Click()

    Dim RowCount As Long

    'Ensure Origination area field is filled
    If Me.cboOrigArea.Value = "" Then
    MsgBox "Please enter an Origination area.", vbExclamation, "Add a new RCA"
    Me.cboOrigArea.SetFocus
    End If

    'Ensure RCA name field is filled
    If Me.txtRCAname.Value = "" Then
    MsgBox "Please enter an RCA name.", vbExclamation, "Add a new RCA"
    Me.txtRCAname.SetFocus
    End If

    'Ensure RCA code field is filled
    If Me.txtRCAcode.Value = "" Then
    MsgBox "Please enter an RCA reference code.", vbExclamation, "Add a new RCA"
    Me.txtRCAcode.SetFocus
    End If

    'Are you sure?
    iResult = MsgBox("Are you sure you want to add the following new RCA?" & vbCrLf & "" & vbCrLf & cboOrigArea.Value & ": " & _
    txtRCAname.Value & " (" & txtRCAcode.Value & ")", vbYesNo + vbInformation, "Are you sure?")

    'Write to 'Data' tab in workbook
    RowCount = Worksheets("RCA_Data").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("RCA_Data").Range("A1")
    .Offset(RowCount, 0) = Me.cboOrigArea.Value
    .Offset(RowCount, 1) = Me.txtRCAname.Value
    .Offset(RowCount, 2) = Me.txtRCAcode.Value
    End With




    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA User Form Check all Fields Filled Combo box

    1 Check the ListIndex of the combobox, if it's -1 they've not selected something on the list.

    2 Depends how you want to handle fields not being filled in.

    One thing you could do is add Exit Sub to each of the If statements that do the checks, then if a field is not filled in the code will end and focus will be on theunfilled field
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. combo box user form code for two combo boxes
    By robert.begley1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-01-2012, 02:25 PM
  2. Generate a new worksheet each time a user form is filled out
    By brandoom989 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2012, 03:40 PM
  3. User Form to check data before closing form
    By CityMPLSEmpolyee in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-15-2012, 12:42 PM
  4. User Form to save Form entries & check for duplicates
    By KPK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2010, 05:44 AM
  5. Filling form fields via worksheet dropdowns, user update via form, change form color
    By Demosthenes&Locke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2010, 08:58 AM

Tags for this Thread

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