CODEWITHSUNDEEP
vbaexcelcombobox
Monchhichi
Monchhichi

Reputation: 397

Setting validation for combo box error

# updated codes 

Function condition(ByRef objCmb As ComboBox)
    If objCmb.Value ="" And objCmb.Value = "g"  Then
        Call MsgBox("gg", vbOKOnly, "error")
    End If
End Function

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition (ComboBox1)
End Sub

'other codes for reference:
Private Sub CommandButton1_Click()
    Dim lastrow As Integer
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row


   For i = 1 To 3
        For j = 1 To 5
            With Me.Controls("ComboBox" & (i - 1) * 5 + j)
                If .Text <> "" Then
                Cells(lastrow + i, j) = .Text
                Else

                Exit Sub

                End If

            End With
        Next j
    Next i


End Sub

I have 50 combo and text boxes in VBA user panel. As it is too troublesome to set constraints in every combo or text box, I want a function to apply to every combo and text box.

For the codes above , it shows up cant find objecterror

How to solve ?

Btw , how to set the function statement for textbox ? is it Function condition2(ByRef objCmb As textbox)...

Upvotes: 1

Views: 160

Answers (1)

user6432984
user6432984

Reputation:

You are receiving an error because ComboBox is not ByRef objCmb As ComboBox. Don't use parenthesis when calling a sub. Don't use parenthesis when calling function if you are not using the functions return value. If a function does not return a value it should be a sub.

Sub condition(ByRef objCmb As MSForms.ComboBox)
    If objCmb.Value <> "" And objCmb.Value = "g" Then
        MsgBox "gg", vbOKOnly, "error"
        objCmb.Value = ""
    End If
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition  ComboBox1
End Sub

I wrote a function to help you generate the Exit event code for all your text and combo boxes.

Sub AddCodeToCipBoard(frm As UserForm)

    Const BaseCode = "    Private Sub @Ctrl_Exit(ByVal Cancel As MSForms.ReturnBoolean)" & vbCrLf & _
          "        condition ComboBox1" & vbCrLf & _
          "    End Sub" & vbCrLf & vbCrLf

    Dim s As String
    Dim ctrl
    Dim clip As DataObject
    Set clip = New DataObject

    For Each ctrl In frm.Controls
        If TypeName(ctrl) = "ComboBox" Or TypeName(ctrl) = "TextBox" Then
            s = s & Replace(BaseCode, "@Ctrl", ctrl.Name)
        End If
    Next

    clip.SetText s

    clip.PutInClipboard

End Sub

Put this code in a module and call it like this:

AddCodeToCipBoard Userform1

Now all the Exit event code will be copied into the Windows Clipboard. Go into your Userforms code module and paste the new code.

Example Output:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition ComboBox1
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition ComboBox1
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition ComboBox1
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition ComboBox1
End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    condition ComboBox1
End Sub

Upvotes: 1

Related Questions