User Form Combobox Property problem?

[nicolavision]

Hi

I need to know how to set the property of a combobox on a user form so that it must contain data and stop the user from moving to the next control until they have selected a option? Is this possible?

Thanks

 

[Legacy 98055]

There are several ways. Here is one simple way to force the user to make a selection from your combo before continuing or closing the userform. Open a new workbook, add a userform, follow the directions in the code's comments. Here is an example to download if you wish.

ForceComboSelection.xls



<font color="#008000">'userform with 3 controls</font>
<font color="#008000">'cmdContinue, cmdCancel, ComboBox1</font>

<font color="#0000A0">Private</font> UserCancelled <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>

<font color="#008000">'If you need the ability to cancel the userform even if a selection</font>
<font color="#008000">'has not yet been made, set the TakeFocusOnClick property</font>
<font color="#008000">'of your Cancel commandbutton = true</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> UserForm_Initialize()
    <font color="#008000"> 'add several items to the combobox</font>
    <font color="#008000"> 'also sets the text displayed = "Select Item"</font>
    <font color="#008000"> 'the ListIndex Property will always = -1 in this case</font>
     <font color="#0000A0">With</font> ComboBox1
         .AddItem "Item 01"
         .AddItem "Item 02"
         .AddItem "Item 03"
         .Text = "Select Item"
         .SetFocus
     <font color="#0000A0">End</font> <font color="#0000A0">With</font>
    
    <font color="#008000"> 'cancel button</font>
     cmdCancel.TakeFocusOnClick = <font color="#0000A0">False</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> ComboBox1_Exit(ByVal Cancel <font color="#0000A0">As</font> MSForms.ReturnBoolean)
    <font color="#008000"> 'remember that the ListIndex Property will = -1 when the form is loaded</font>
    <font color="#008000"> 'the ListIndex cannot = -1 if the user has made a selection</font>
    <font color="#008000"> 'if no selection has been made then (ComboBox1.ListIndex = -1) will = True</font>
    <font color="#008000"> 'if Cancel = True then the Exit is cancelled. The focus remains on the combobox</font>
     <font color="#0000A0">If</font> <font color="#0000A0">Not</font> UserCancelled <font color="#0000A0">And</font> (ComboBox1.ListIndex = -1) <font color="#0000A0">Then</font>
         Cancel = <font color="#0000A0">True</font>
         MsgBox "You must <font color="#0000A0">select</font> an item <font color="#0000A0">to</font> continue..."
     <font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> cmdContinue_Click()
    <font color="#008000"> 'other code here</font>
     Unload Me
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> cmdCancel_Click()
    <font color="#008000"> 'flag to bypass warning message</font>
     UserCancelled = <font color="#0000A0">True</font>
     Unload Me
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> UserForm_QueryClose(Cancel <font color="#0000A0">As</font> Integer, CloseMode <font color="#0000A0">As</font> Integer)
<font color="#999999">1</font>    
<font color="#999999">2</font>     <font color="#008000"> 'does not allow the user to close the userform with the Close("X")</font>
<font color="#999999">3</font>      <font color="#0000A0">If</font> CloseMode = 0 <font color="#0000A0">And</font> (ComboBox1.ListIndex = -1) <font color="#0000A0">Then</font>
<font color="#999999">4</font>          Cancel = <font color="#0000A0">True</font>
<font color="#999999">5</font>          MsgBox "You must <font color="#0000A0">select</font> an item <font color="#0000A0">to</font> continue..."
<font color="#999999">6</font>      <font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#999999">7</font>    
<font color="#999999">8</font>     <font color="#008000"> 'comment out lines 3 to 6</font>
<font color="#999999">9</font>     <font color="#008000"> 'un-comment line 10 treat the Close("X") as a cancel</font>
<font color="#999999">10</font>    <font color="#008000"> 'If CloseMode = 0 Then UserCancelled = True</font>
<font color="#999999">11</font>   
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

 

[nicolavision]

Thanks for that but I'm struggling with the answer. Currently I have a user form that has about 14 combo box/text boxes and I want to make it so that when the user click OK (cmdOK) if any of the controls are blank that stop them from continuing

What would be the code for this?

 

[Norie]

Well you can loop through all the controls in a userform like this:

Private Sub cmdOK_Click()
Dim ctl As MSForms.Control
Dim boolCompleted As Boolean

    boolCompleted = True
    For Each ctl In Me.Controls
        
        If (TypeOf ctl Is MSForms.TextBox) Or (TypeOf ctl Is MSForms.ComboBox) Then
            boolCompleted = boolCompleted And ctl.Value <> ""
        End If
        
    Next
    
    If Not boolCompleted Then
        MsgBox "You have not completed all the fields on the form!", vbCritical, "Input error"
    Else
        ' run other code for when all fields completed
    End If
    
End Sub

 

[nicolavision]

That's working great apart from now after it comes up with the message it then runs my on error function and closes down the workbook.

How can I get it to just keep looping until the user has filled in all the required fields and then run the else?

Here's my code

Private Sub cmdOK_Click()
On Error GoTo Handler

Private Sub cmdOK_Click()
Dim ctl As MSForms.Control
Dim boolCompleted As Boolean

    boolCompleted = True
    For Each ctl In Me.Controls
       
        If (TypeOf ctl Is MSForms.TextBox) Or (TypeOf ctl Is MSForms.ComboBox) Then
            boolCompleted = boolCompleted And ctl.Value <> ""
        End If
       
    Next
   
    If Not boolCompleted Then
        MsgBox "You have not completed all the fields on the form!", vbCritical, "Input error"
    Else
      

ActiveWorkbook.Sheets("User Information").Activate
Range("A1:B15").Select
Selection.Copy
Workbooks.Add
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
ChDir "H:\"
    ActiveWorkbook.SaveAs Filename:="H:\UserInformation.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End If
Handler:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Unload Me
Application.DisplayAlerts = False
Sheet1.Visible = xlSheetHidden
ActiveWorkbook.Sheets("Standard Expense Claim").Activate
Range("A5").Select

End Sub

Thanks

 

[Norie]

Perhaps change this:

 If Not boolCompleted Then 
        MsgBox "You have not completed all the fields on the form!", vbCritical, "Input error"

to

If Not boolCompleted Then 
        MsgBox "You have not completed all the fields on the form!", vbCritical, "Input error
        Exit Sub

BTW you don't need to activate/select workbooks/sheets and ranges to work with them.

eg

This

ActiveWorkbook.Sheets("User Information").Activate 
Range("A1:B15").Select 
Selection.Copy

could be replaced with this:

ActiveWorkbook.Sheets("User Information").Range("A1:B15").Copy

 

[nicolavision]

Thanks for that - works great

 

[nicolavision]

Hi

I'm still having a problem the loop works fine but the user form opens and hides some controls - how to I get it to not force the hidden controls to be completed?

Thanks

 

[Norie]

How are you hiding the controls?

You could test whether the control is hidden using it's Visible property. Maybe something like this:

     If (TypeOf ctl Is MSForms.TextBox) Or (TypeOf ctl Is MSForms.ComboBox) Then 
            If ctl.Visible = True Then
                  boolCompleted = boolCompleted And ctl.Value 
<> "" 
            End If
        End If

 

[nicolavision]

Hi
That great thanks for all your help

Nicola