+ Reply to Thread
Results 1 to 2 of 2

Userform: requiring fields and using a check box to set default option boxes

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Userform: requiring fields and using a check box to set default option boxes

    I have a userform that contains 4 combo boxes at the top (labeled cbo(n)) and 2 text boxes (labeled txt1 and txt2). I then have a stand alone check box (chk1) that I want to act as a short cut... more in a minute. After that, I have 12 frames which each contain 2 option buttons, one for Yes (labeled option(n)Y) and one for "No" (these are labeled option(n)N - but are not involved in the data transfer command), 24 buttons total. At the bottom of the form I have another frame which has 9 more check boxes (chk(2-10)). And finally, one last text box used for short memos (txt3).

    Below is my code for my "Save & Close" command button to transfer all the data to another worksheet (ws3, "data").

    Now I need to write the code for my 'business rules.' This post will just be Part 1 of 2 or 3 rules.

    The rules are:
    1) all of the first 4 combo boxes and the first 2 text boxes are required, or I'd like a message pop up to say all the fields must be completed.
    2) all of the 12 frames with the options boxes are also required (either Yes or No) - EXCEPT
    3) if the first check box (chk1) is true, then all options should default to "Yes" and cannot be changed to "No" unless the check box is unchecked.

    In this last rule, the first check box acts as a shortcut, that if the user wants to answer everything yes, they won't have to go through and click each one (when the form initializes, the option buttons get set to false, neither yes or no checked by default).

    Here's my code, thus far... (haven't made an attempt to try the rules above, sorry... well actually, I have, but it failed miserably

    Private Sub SaveClose_Click()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Dashboard")
    Dim ws3 As Worksheet:   Set ws3 = Sheets("Data")
    
    'transfer combo and text box data
    Dim emptyRow As Long
    With ws3
    emptyRow = WorksheetFunction.CountA(ws3.Range("$C:$C")) + 1
    .Cells(emptyRow, 2).Value = Date
    .Cells(emptyRow, 3).Value = cbo1.Value
    .Cells(emptyRow, 4).Value = cbo2.Value
    .Cells(emptyRow, 5).Value = txt1.Value
    .Cells(emptyRow, 6).Value = cbo3.Value
    .Cells(emptyRow, 7).Value = cbo4.Value
    .Cells(emptyRow, 8).Value = txt2.Value
    .Cells(emptyRow, 34).Value = txt3.Value
    
    'transfer option buttons data
    If option1Y.Value = True Then
        .Cells(emptyRow, 13).Value = 1
    Else
        .Cells(emptyRow, 13).Value = 2
    End If
    
    If option2Y.Value = True Then
        .Cells(emptyRow, 14).Value = 1
    Else
        .Cells(emptyRow, 14).Value = 2
    End If
    
    If option3Y.Value = True Then
        .Cells(emptyRow, 15).Value = 1
    Else
        .Cells(emptyRow, 15).Value = 2
    End If
    
    If option4Y.Value = True Then
        .Cells(emptyRow, 16).Value = 1
    Else
        .Cells(emptyRow, 16).Value = 2
    End If
    
    If option5Y.Value = True Then
        .Cells(emptyRow, 17).Value = 1
    Else
        .Cells(emptyRow, 17).Value = 2
    End If
    
    If option6Y.Value = True Then
        .Cells(emptyRow, 18).Value = 1
    Else
        .Cells(emptyRow, 18).Value = 2
    End If
    
    If option7Y.Value = True Then
        .Cells(emptyRow, 19).Value = 1
    Else
        .Cells(emptyRow, 19).Value = 2
    End If
    
    If option8Y.Value = True Then
        .Cells(emptyRow, 20).Value = 1
    Else
        .Cells(emptyRow, 20).Value = 2
    End If
    
    If option9Y.Value = True Then
        .Cells(emptyRow, 21).Value = 1
    Else
        .Cells(emptyRow, 21).Value = 2
    End If
    
    If option10Y.Value = True Then
        .Cells(emptyRow, 22).Value = 1
    Else
        .Cells(emptyRow, 22).Value = 2
    End If
    
    If option11Y.Value = True Then
        .Cells(emptyRow, 23).Value = 1
    Else
        .Cells(emptyRow, 23).Value = 2
    End If
    
    If optionOtherY.Value = True Then
        .Cells(emptyRow, 24).Value = 1
    Else
        .Cells(emptyRow, 24).Value = 2
        
    'transfer check boxes data
    If chk2.Value = True Then .Cells(emptyRow, 25).Value = 1
    
    If chk3.Value = True Then .Cells(emptyRow, 26).Value = 1
    
    If chk4.Value = True Then .Cells(emptyRow, 27).Value = 1
    
    If chk5.Value = True Then .Cells(emptyRow, 28).Value = 1
    
    If chk6.Value = True Then .Cells(emptyRow, 29).Value = 1
    
    If chk7.Value = True Then .Cells(emptyRow, 30).Value = 1
    
    If chk8.Value = True Then .Cells(emptyRow, 31).Value = 1
    
    If chk9.Value = True Then .Cells(emptyRow, 32).Value = 1
    
    If chk10.Value = True Then .Cells(emptyRow, 33).Value = 1
    
    End If
    
    End With
    
    Unload Me
    
    End Sub

  2. #2
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Userform: requiring fields and using a check box to set default option boxes

    With no responses yet, I thought I'd try again and attach my workbook. Thanks!

    -HeyInKy
    Attached Files Attached Files

+ 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. [SOLVED] UserForm Auto Check check boxes if TextBoxes are not empies
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2013, 11:36 AM
  2. Activate Print option with a userform where selected Sheet check box option available..
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 08:19 AM
  3. Check boxes and Option buttons
    By Chris Martin in forum Excel General
    Replies: 2
    Last Post: 05-15-2009, 04:21 AM
  4. option buttons or check boxes
    By meandmyindigo via OfficeKB.com in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-07-2006, 08:00 AM
  5. Replies: 1
    Last Post: 02-09-2006, 06:35 PM

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