Results 1 to 9 of 9

Thread: Userform problem [Message box for missing entry in opt and chk button]

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0

    Userform problem [Message box for missing entry in opt and chk button]

    Good day sir/madam!
    Im using windows 7 excel 2010 and save it as xlsm

    Kindly help me on my code


    Code:
        If (optAAA.Value) = "" Then
        ElseIf (optBBB.Value) = "" Then
        ElseIf (optCCC.Value) = "" Then
        Else
        optAAA.SetFocus
        MsgBox "Pls choose product"
        Exit Sub
        End If
    I try using this code but I got stuck on the option button maybe I'm missing something.

    I also had a problem on putting a checkbox code in 'testing incomplete entry.

    ORIGINAL CODE

    Code:
     Private Sub cmdOK_Click()
        ActiveWorkbook.Sheets("Sheet1").Activate
        Range("B5").Select
        Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
        Loop Until IsEmpty(ActiveCell) = True
           
    'Test for incomplete entry
        If (txtDate.Value) = "" Then
        txtDate.SetFocus
        MsgBox "Please enter a date of exit"
        Exit Sub
        
        End If
        If (cboBranch.Value) = "" Then
        cboBranch.SetFocus
        MsgBox "Please enter a branch"
        Exit Sub
        End If
    
        If (optAAA.Value) = "" Then
        ElseIf (optBBB.Value) = "" Then
        ElseIf (optCCC.Value) = "" Then
        Else
        optAAA.SetFocus
        MsgBox "Pls choose a product"
        Exit Sub
        End If
    
        
        ActiveCell.Value = txtDate.Value
        ActiveCell.Offset(0, 1) = cboBranch.Value
        ActiveCell.Offset(0, 2) = txtSName.Value
        ActiveCell.Offset(0, 3) = txtFName.Value
        ActiveCell.Offset(0, 4) = txtMI.Value
        ActiveCell.Offset(0, 5) = txtCID.Value
    
    
        If optAAA = True Then
            ActiveCell.Offset(0, 6).Value = "AAA"
        ElseIf optBBB = True Then
            ActiveCell.Offset(0, 6).Value = "BBB"
        ElseIf optCCC = True Then
            ActiveCell.Offset(0, 6).Value = "CCC"
        Else
            ActiveCell.Offset(0, 6).Value = ""
        End If
    
    
        If chkXXX = True Then
            ActiveCell.Offset(0, 7).Value = "XXX"
        Else
            ActiveCell.Offset(0, 7).Value = ""
        End If
        If chkYYY = True Then
            ActiveCell.Offset(0, 8).Value = "YYY"
        Else
            ActiveCell.Offset(0, 8).Value = ""
        End If
        If chkZZZ = True Then
            ActiveCell.Offset(0, 9).Value = "ZZZ"
        Else
            ActiveCell.Offset(0, 9).Value = ""
        End If
    
    
    'clearing forms
        Range("B5").Select
        txtDate.Value = ""
        cboBranch.Value = ""
        txtSName.Value = ""
        txtFName.Value = ""
        txtMI.Value = ""
        txtCID.Value = ""
        optAAA = False
        optBBB = False
        optCCC = False
        chkXXX = False
        chkYYY = False
        chkZZZ = False
        cmdExtra.SetFocus
    
    End Sub

    Thank You Very Much

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Are the controls activex controls or form controls? If you are not sure, just mention whether you are seeing
    Code:
    =EMBED("Forms.XXX.1","")
    in the formula bar when you select the control? where XXX can be OptionButton or ComboBox
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    Are the controls activex controls or form controls?
    BUMP

    VBA user form control

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Option Button value give either TRUE or FALSE and not ""

    so try

    Code:
    If Not (optAAA.Value) + (optBBB.Value) + (optCCC.Value) Then
        optCCC.SetFocus
        MsgBox "Pls choose product"
        Exit Sub
    End If
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Thank you very much sir your code works fine

    Heres the code I use before and put 'Test for incomplete entry

    But I will use your code
    Code:
        If optAAA = True Then
            ActiveCell.Offset(0, 6).Value = "AAA"
        ElseIf optBBB = True Then
            ActiveCell.Offset(0, 6).Value = "BBB"
        ElseIf optCCC = True Then
            ActiveCell.Offset(0, 6).Value = "CCC"
        Else
            ActiveCell.Offset(0, 6).Value = ""
        fraProduct.SetFocus
        MsgBox "Pls Choose product"
        Exit Sub   
     End If

    How about in checkbox? Any ideas sir
    Last edited by Excel Fox; 05-21-2013 at 08:32 AM. Reason: Quote Removed

  6. #6
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by Admin View Post
    Hi

    Option Button value give either TRUE or FALSE and not ""

    Sir Admin Based on your code it seems to be the and Checkbox Button also works but only for 1 box.
    Assuming 2 checkbox or 3 checkbox will be tick

    Kindly help me
    Last edited by ranthrave; 05-20-2013 at 02:53 PM.

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Not sure about the requirement. Are you looking something like ?

    Code:
    Dim Ctrls, i As Long
        
        Ctrls = Array(CheckBox1, CheckBox2, CheckBox3)
        
        For i = LBound(Ctrls) To UBound(Ctrls)
            If Ctrls(i).Value Then 'if TRUE
                ActiveCell.Offset(, i + 1) = "Some Value"
            Else
                'unchecked
            End If
        Next
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Sir
    I'm looking something similar to your previous code
    Code:
    If Not (optAAA.Value) + (optBBB.Value) + (optCCC.Value) Then
        optCCC.SetFocus
        MsgBox "Pls choose product"
        Exit Sub
    End If
    Similar case, but instead of option button I will use checked button

    Code:
    If Not (chkXXX.Value) + (chkYYY.Value) + (chkZZZ.Value) Then
        chkXXX.SetFocus
        MsgBox "Pls choose 2 or 3 brand"
        Exit Sub
    End If
    Last edited by Excel Fox; 05-21-2013 at 08:32 AM. Reason: Quote Removed

  9. #9
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    I forgot the "Else".

    Anyway just wanted to say Many Thanks sir Admin for your time and solutions

Similar Threads

  1. Solve Block If Without End If Problem
    By jffryjsphbyn in forum Excel Help
    Replies: 3
    Last Post: 06-12-2013, 11:06 AM
  2. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  3. Message Box Pop-Up "yes or no"
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 02-19-2013, 06:20 PM
  4. Display sheet names in a message box
    By pells in forum Excel Help
    Replies: 4
    Last Post: 02-13-2013, 07:33 PM
  5. Message Box Before Saving Document
    By Lucero in forum Excel Help
    Replies: 2
    Last Post: 04-15-2012, 07:09 AM

Posting Permissions

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