List Empty ActiveX Text Boxes

[Daniel_Roberts]

Hello all,

I'm building a worksheet with several ActiveX text boxes for data entry. I'm trying to write code that would prompt the user with a message box listing any text boxes that they have not completed before moving the entries to the spreadsheet. When I run my code, it doesn't return an error, however it also doesn't seem to work... Thanks in advance!

Sub TEST()
Dim fTextBox As OLEObject
Dim xTxtName As String
Dim xEptTxtName As String
For Each fTextBox In Sheet2.OLEObjects
    If TypeName(fTextBox) = "TextBox" Then
        If fTextBox.Text = "" Then
            xEptTxtName = xEptTxtName & fTextBox.Name & vbNewLine
        End If
    End If
Next
If xEptTxtName <> "" Or xTxtName <> "" Then
    MsgBox ("Please provide the following information:" & vbNewLine & "" & vbNewLine & xEptTxtName & vbNewLine & xTxtName)
End If
End Sub

 

[dmt32]

Hi,
welcome to forum

try this update to your code

Sub TEST()
    Dim fTextBox As OLEObject
    Dim xTxtName As String
    Dim xEptTxtName As String

    For Each fTextBox In Sheet2.OLEObjects
        If TypeOf fTextBox.Object Is msforms.TextBox Then
            If Len(fTextBox.Object.Text) = 0 Then
                xEptTxtName = xEptTxtName & fTextBox.Name & vbNewLine
            End If
        End If
    Next

    If Len(xEptTxtName) > 0 Then
        MsgBox "Please provide the following information:" & _
        vbNewLine & "" & vbNewLine & _
        xEptTxtName & vbNewLine & _
        xTxtName, 48, "Entry Required"
    End If
End Sub

Dave

 

[Yongle]

you could try this..

Sub EmptyTextboxes()
    Dim sh As Shape, xEptTxtName As String
    For Each sh In Sheet2.Shapes
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "TextBox" Then
                If sh.OLEFormat.Object.Object = "" Then xEptTxtName = xEptTxtName & sh.Name & vbCr
            End If
        End If
    Next sh
    If xEptTxtName <> "" Then MsgBox ("Please provide the following information:" & vbNewLine & "" & vbCr & xEptTxtName)
End Sub

 

[Daniel_Roberts]

Hi,
welcome to forum

try this update to your code

Sub TEST()
    Dim fTextBox As OLEObject
    Dim xTxtName As String
    Dim xEptTxtName As String

    For Each fTextBox In Sheet2.OLEObjects
        If TypeOf fTextBox.Object Is msforms.TextBox Then
            If Len(fTextBox.Object.Text) = 0 Then
                xEptTxtName = xEptTxtName & fTextBox.Name & vbNewLine
            End If
        End If
    Next

    If Len(xEptTxtName) > 0 Then
        MsgBox "Please provide the following information:" & _
        vbNewLine & "" & vbNewLine & _
        xEptTxtName & vbNewLine & _
        xTxtName, 48, "Entry Required"
    End If
End Sub

Dave

This worked perfect! I'm hoping to tweak it a little bit so the message box list includes combo boxes too? Any ideas?

And is there a way that I could exclude a couple text boxes?

Thanks!

 

[dmt32]

This worked perfect! I'm hoping to tweak it a little bit so the message box list includes combo boxes too? Any ideas?

And is there a way that I could exclude a couple text boxes?

Thanks!

try this update

Sub TEST()
    Dim fTextBox As OLEObject
    Dim xTxtName As String, xEptTxtName As String
    Dim TobeExcluded As Boolean
    
    For Each fTextBox In Sheet2.OLEObjects
        Select Case True
        Case TypeOf fTextBox.Object Is MSForms.TextBox, TypeOf fTextBox.Object Is MSForms.ComboBox
        TobeExcluded = Not IsError(Application.Match(fTextBox.Name, Array("TextBox1", "TextBox4"), 0))
        
        If Not TobeExcluded Then
            If Len(fTextBox.Object.Text) = 0 Then
                xEptTxtName = xEptTxtName & fTextBox.Name & vbNewLine
            End If
        End If
        
        End Select
    Next
    
    If Len(xEptTxtName) > 0 Then
        MsgBox "Please provide the following information:" & _
        vbNewLine & "" & vbNewLine & _
        xEptTxtName & vbNewLine & _
        xTxtName, 48, "Entry Required"
    End If
    
End Sub

add / update the name(s) of the controls you want to exclude from the check in the array shown in RED.

Dave