Check if textbox exists in userform using name

Question

I created an Ms-Access userform which has a number of Textboxes. The boxes are named: Box1, Box2, Box3 ...

I need to loop through all boxes, but I don't know which is the last one. To avoid looping through all userform controls I thought of the following:

For i =1 To 20

If Me.Controls("Box" & i).value = MyCondition Then
    'do stuff
End If
   
Next i

This errors at Box6, which is the first box not found. Is there a way to capture this error and exit the loop?

I could use On Error but I 'd rather capture this specific instance with code.

Answer 1

I found this post from a search for "ms word 365 vba how to determine if a specific textbox exists in document".

In my case, I am creating a drawing in MS Word. I have several "Drawing Objects" that are grouped together & multiple copies of similar groups that contain different text (ID #'s).

So in an effort to avoid re-inventing the wheel every time I produce a new drawing, I open an existing drawing, SaveAs w/ a new name, then use VBA to re-position my drawing objects on the side of the doc before altering the drawing & placing the ID #'s in their correct locations on the new doc.

Depending on the type of drawing that I'm creating, some drawings will have a certain "Drawing Object", while others will not. So I use the following to determine if that specific object exists or not ...

Private Sub sort_Nums()
  Dim grp As Shape, shp As Object
  Dim id As String    '<-- object id - could be a # &/or a letter
  Dim flg_It_Exists As Boolean

  Set doc = Application.ActiveDocument
  flg_It_Exists = False

  For Each grp In doc.Shapes
    If grp.Type = 6 Then    '<-- if shape is a group then
      For Each shp In grp.GroupItems
        If shp.Type = 17 Then    '<-- if group contains a textbox then
          'pop var "id" to get ID, trim & strip all vbCR/vbLR/vbCRLF
          If IsNumeric(id) Then
            'manage stuff for # ID
          ElseIf [id=string ID] Then
            'manage stuff for string ID
          ElseIf [id=specific ID] Then
            'manage stuff for specific ID
            flg_It_Exists = True
          End If
        End If
      Next shp
    End If
  Next grp

  'reposition specific object
  If flg_It_Exists Then
    'move specific object to its default starting position
  End If

  'code to manage other objects here
  
  Set doc = Nothing
End Sub

I realize that the OP is asking about a UserForm in MS Access, but the underlying logic might still apply.

Answer 2

You have taken an incorrect approach here.

If you want to limit the loop, you can loop only in the section your controls reside e.g. Detail. You can use the ControlType property to limit controls to TextBox.

Dim ctl As Control
For Each ctl In Me.Detail.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.Value = MyCondition Then
            'do stuff
        End If
    End If
Next ctl

I believe the loop will be faster than checking if the control name exists through a helper function and an On Error Resume Next.

But this only a personal opinion.

Answer 3

A Controls collection is a simplified collection of controls (obviously) and share a same order as a placement order of controls.

First of all, even a creatable collection object lacks methods such as Exists or Contains , hence you need a function with error handling to checking/pulling widget from a collection.

Public Function ExistsWidget(ByVal Name As String) As Boolean
    On Error Resume Next
        ExistsWidget = Not Me.Controls(Name) Is Nothing
    On Error GoTo 0
End Function

If you really doesnt like "ask forgiveness not permission" option you can pull entire ordered collection of your textboxes (and/or check existance by name in another loop with similar logic).

Public Function PullBoxes() As Collection
    Dim Control As MSForms.Control

    Set PullBoxes = New Collection

    For Each Control In Me.Controls
        If TypeOf Control Is MSForms.TextBox And _
                Left(Control.Name, 3) = "Box" Then
                Call PullBoxes.Add(Control)
        End If
    Next
End Function

Since names of widgets are unique - you can return a Dictionary from that function with (Control.Name, Control) pairs inside and able to check existance of widget by name properly w/o an error suppression. There's a good guide to Dictionary if it's a new information for you.

Anyway, no matter what object you choose, if user (or code) is unable to create more of thoose textboxes - you can convert this Function above to a Static Property Get or just to a Property Get with Static collection inside, so you iterate over all controls only once (e.g. on UserForm_Initialize event)!

Public Property Get Boxes() As Collection
    Static PreservedBoxes As Collection

    'There's no loop, but call to PullBoxes to reduce duplicate code in answer
    If PreservedBoxes Is Nothing Then _
            Set PreservedBoxes = PullBoxes

    Set Boxes = PreservedBoxes
End Property

After all, the last created TextBox with name Box* will be:

Public Function LastCreatedBox() As MSForms.TextBox
    Dim Boxes As Collection

    Set Boxes = PullBoxes

    With Boxes
        If .Count <> 0 Then _
                Set LastCreatedBox = Boxes(.Count)
    End With
End Function

I think that now things are clearer to you! Cheers!

Note: All code are definitely a bunch of methods/properties of your form, hence all stuff should be placed inside of form module.

Answer 4

I would suggest testing the existence in another procedure per below: -

Private Sub Command1_Click()
Dim i As Long

i = 1
Do Until Not BoxExists(i)
    If Me.Conrtols("Box" & i).Value = MyCondition Then
        'Do stuff
    End If
    i = i + 1
Next
End Sub

Private Function BoxExists(ByVal LngID As Long) As Boolean
Dim Ctrl As Control

On Error GoTo ErrorHandle

Set Ctrl = Me.Controls("BoX" & LngID)
Set Ctrl = Nothing

BoxExists = True

Exit Function
ErrorHandle:
Err.Clear
End Function

In the above, BoxExists only returns true if the box does exists.

Answer 5

Long story short - you cannot do what you want with VBA. However, there is a good way to go around it - make a boolean formula, that checks whether the object exists, using the On Error. Thus, your code will not be spoiled with it.

Function ControlExists(ControlName As String, FormCheck As Form) As Boolean
   Dim strTest As String
   On Error Resume Next
   strTest = FormCheck(ControlName).Name
   ControlExists = (Err.Number = 0)
End Function

Taken from here:http://www.tek-tips.com/viewthread.cfm?qid=1029435

To see the whole code working, check it like this:

Option Explicit

Sub TestMe()

    Dim i       As Long

    For i = 1 To 20
        If fnBlnExists("Label" & i, UserForm1) Then
            Debug.Print UserForm1.Controls(CStr("Label" & i)).Name & " EXISTS"
        Else
            Debug.Print "Does Not exist!"
        End If
    Next i

End Sub

Public Function fnBlnExists(ControlName As String, ByRef FormCheck As UserForm) As Boolean

    Dim strTest As String
    On Error Resume Next
    strTest = FormCheck(ControlName).Name
    fnBlnExists = (Err.Number = 0)

End Function