VBA UserForm Search and Update Me.Controls Issue

[JHud2022]

Hello! I have been so close to getting this new feature for my UserForm completed but I am running into an issue with my Me.Controls. When testing out my search function with the UserForm by hitting Enter, it gives me a Run-time error about not being able to find the specified object. I am working within a data table, so I have it as Me.Controls("SunSch" & i) which is my ListObject. I may just be thinking about this two hard, but I just can't seem to figure it out. Any assistance would greatly be appreciated.

Option Explicit


Dim fnd             As Range

Enum XLRecordActionType
   
    xlGetRecord
    xlUpdateRecord
    xlClearForm
   
End Enum


Private Sub UserForm_Initialize()

    Me.NewStoreTextBox.SetFocus
    Me.UPDATE.Enabled = False
   
End Sub

Private Sub UPDATE_Click()

    GetUpdateRecord xlUpdateRecord
   
End Sub

Private Sub NewStoreTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = vbKeyReturn Then Findit
   
End Sub

Private Sub NewStoreTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Cancel = fnd Is Nothing
   
End Sub

Private Sub Findit()
  
    Dim i           As Integer
    Dim SEARCH      As String
    Dim sh          As Worksheet
  
    Set sh = Sheet1
    SEARCH = Me.NewStoreTextBox.Text
    If Len(SEARCH) = 0 Then Exit Sub
   
    Set fnd = sh.Columns("A:A").Find(SEARCH, , , xlWhole)
  
    If Not fnd Is Nothing Then
        GetUpdateRecord xlGetRecord
    Else
        MsgBox "Store Number Not Found", 48, "Input Error"
    End If
   
End Sub

Sub GetUpdateRecord(ByVal Action As XLRecordActionType)

    Dim i       As Integer
  
    For i = 1 To 31
   
        With Me.Controls("SunSch" & i)
        If Action = xlGetRecord And i > 1 Then .Text = fnd.Offset(, i - 1).Text
        If Action = xlUpdateRecord Then fnd.Offset(, i - 1).Value = .Text
        If Action <> xlGetRecord Then .Text = ""
        If i = 1 Then .SetFocus
        End With
       
    Next i
   
    Me.UPDATE.Enabled = CBool(Action = xlGetRecord)
   
    If Action <> xlGetRecord Then Set fnd = Nothing
   
End Sub

 

[Kyle123]

am working within a data table, so I have it as Me.Controls("SunSch" & i) which is my ListObject.

This bit doesn’t sound right, the control names should refer to the text box names on your userform. The error would appear to indicate that it can’t find the relevant control, do you have 31 text boxes on your user from names SunSch1 to SunSch31? Are you sure that they’re all named correctly and there aren’t any typos?

 

[JHud2022]

I have 31 text boxes on the userform, but each has a unique name. SunSch is my table range as I just need the vba to apply to my table. In order to use the control names, do I need all of the text boxes to have a universal name, such as TextBox1, TextBox2, TextBox3, etc.?

 

[dmt32]

Your code looks very much like a modified version of one I created for another OP on this forum & for it to work you need to have your control names (textboxes) using a common naming convention with a suffix number e.g TextBox1 TextBox2 etc
or you can read your controls into an array & index each array element in the loop.

 With Me.Controls("TextBox" & i)

Dave

 

[Kyle123]

Yes, controls related to the name of the control you want to read/write to. It this context, it’s nothing to do with the table, you’ve already set the range so the table is an irrelevance