VBA Multiple Textbox Exit Event Question

[Annie Bender]

Hi guys. Been a while since I needed your help, but I'm back again. The project: Form with lots of textbox controls (dynamic, up to ~150). I want to do validation upon exiting these controls, but don't want to have a separate Textbox_Exit sub for each one of them to call the identical validation test (contained in one validation sub). How can a single "generic" Textbox_Exit sub be written to apply to each of the 150+/- controls that require validation? Is this where a class comes in? If so, that's new programming to me, so some guidance would really be appreciated. Actually, however it has to be done, I need your help. Thanks as always for straightening me out.

--Annie in Florida

 

[Norie]

Annie

A listbox would have no problem storing data like that.

The sorting perhaps not, but how were you planning to deal with that with so many textboxes anyway?

The next step up from a listbox is a ListView, which has built-in sorting.

They have other advantages, but populating them can be a bit tricky, St first anyway.

Probably best to start with a listbox.

 

[Norie]

I came up with this to populate the listbox.

Option Explicit

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim col As Range
Dim rw As Range
Dim I As Long
Dim rngParts As Range
    LastRow = Sheets("Replacement Parts").Range("B" & Rows.Count).End(xlUp).Row
    Set rngParts = Sheets("Replacement Parts").Range("B6:B" & LastRow)
    ListBox1.ColumnCount = 7
    For Each rw In rngParts.Cells
        ListBox1.AddItem rw.Value
        For I = 1 To ListBox1.ColumnCount - 1
            Set col = rw.Offset(, I * 2)
            ListBox1.List(ListBox1.ListCount - 1, I) = col.Value
        Next I
    Next rw
End Sub

It's a bit rough and bound to have some mistakes, the main one probably being using the wrong data.

This is how I thought a record 'worked' - B was the first column then D, F and so on.

Basically every other column starting at column B.

 

[ZVI]

... Here is the userform coding: ...

Annie, your textboxes are named as "TextBox#", where # is the textbox number.
Therefore you can calculate textbox number in such a way: i = Val(Mid(Ctrl.Name, 8))

Replace your UserFormCtl_OnExit code by this:

Private Sub UserFormCtl_OnExit(Ctrl As MSForms.Control, Cancel As Boolean)

  Const WARNMSG = "The installation date has changed. Do you wish to adjust the inventory?"
  Dim i As Long

  ' Exit if Ctrl is not TextBox
  If Not TypeOf Ctrl Is MSForms.TextBox Then Exit Sub

  ' Exit if Tag of control is not equal to "1"
  If Not Ctrl.Tag = "1" Then Exit Sub

  ' Calc the number of control after "TextBox" part of its name (starting from 8th char of the name)
  i = Val(Mid(Ctrl.Name, 8))

  ' Compare value of TextBox with arrParts(i,7)
  If Ctrl.Object.Value <> arrParts(i, 7) Then
    Ctrl.BackColor = &HC0C0FF
    If MsgBox(WARNMSG, vbYesNo + vbDefaultButton2) = vbYes Then
      ' vbYes is choosen
      Ctrl.BackColor = &H80000005
      arrParts(i, 7) = Ctrl.Object.Value
      ' Do something here
      ' ...
    Else
      ' vbNo is choosen
      Cancel = True
    End If
  Else
    ' Textbox value is equal to arrParts(i, 7)
    Ctrl.BackColor = &H80000005
  End If

End Sub

Let me know if it works,
Vlad

 

[ZVI]

If numbering of texboxes to be compared with arrParts() are: 7, 15, 23, 31 and so on, then:
replace this line: i = Val(Mid(Ctrl.Name, 8))
by that line: i = Val(Mid(Ctrl.Name, 8)) \ 8 + 1

 

[Annie Bender]

Wow. Thank you so much Vlad and Norie for sticking with me. I'm learning this stuff as fast as I can, but even that seems painfully slow at times. Like now!

@Vlad, I will try the modifications you suggested first. Eventually, I will have to learn about the listbox approach, but I would love to finish the project as it is designed now. I'll report my progress later today. Thank you, nice gentlemen.

--Annie in Florida

 

[Annie Bender]

No good news yet. Stepping through the code, the problem is that the OnExit sub does not fire until I exit the form, rather than the textboxes. Take a look at my post #26 again. I wonder if it has something to do with how the values are initialized in the following step:

' ===== Step 1: Initialize array values from the worksheet and assign those values to textboxes on the form =====
For i = 1 To UBound(arrParts)
    arrParts(i, 1) = Range("B" & i + 6)
    Me.Controls("TextBox" & 1 + iv).Value = arrParts(i, 1)
    Me.Controls("TextBox" & 1 + iv).Visible = True
    For ii = 2 To 7
        iii = 2 * (ii - 1)
        arrParts(i, ii) = Range("B" & i + 6).Offset(0, iii)
        Me.Controls("TextBox" & ii + iv).Value = arrParts(i, ii)
        Me.Controls("TextBox" & ii + iv).Visible = True
    Next ii
    iv = iv + 7
Next i

When the Layout sub goes to the StartWatching sub,

UserFormCtl.StartWatching Me

apparently refers to the entire form itself, and not to the current textbox control. Your example test program works so nice, I can only think that the way I initialize the array and textbox values is the problem. But why??

Would you please take a look at how these values are initialized and why OnExit is not called when leaving textboxes? If I can't figure this out soon, I'm going to go to the beach and eat chocolate. At least I know that will make me feel better.

--Annie

 

[Annie Bender]

VBA Multiple Textbox Exit Event Question:SOLVED

Success! After re-doing my coding numerous times without success, and always seeing that the ActiveControl seemed to be a Frame on the form, rather than a textbox (that's why the OnExit event only fired when I left the form itself rather than leaving a textbox), it finally dawned on me that the textboxes were all contained within a frame control, so the following code might need to be modified to point to the Frame, rather than the form (does that make sense?):

Private Sub UserForm_Layout()
  If UserFormCtl Is Nothing Then
    Set UserFormCtl = New clsWatchEvents
 '   The next line was wrong, b/c 'Me' wasn't sufficient
    UserFormCtl.StartWatching Me
 '   So I replaced it with the following line
    UserFormCtl.StartWatching Me.Frame1
  End If
End Sub

Once the coding "knew" to look inside Frame1, it found the correct ActiveControl to be the textboxes. Such a simple thing, but it kept the whole event from working. I can't thank y'all enough. I feel embarrassed that I put everyone through this, when I should have thought that it must be something like that. That's the problem with a beginner--you don't know what you don't know. At least this might help some future novice avoid endless hours of staring at their coding with no idea of what to do next.

--Annie

 

[ZVI]

Nice to know that you’ve sorted it out, Annie!
As Rorya has already mentioned "Those events actually belong to the container control on the form". Frame is container in your case.
Thank you for the feedback & Good Luck!
Vlad

 

[Annie Bender]

Thanks Vlad, You were a tremendous help. I could not have done this without your patient coaching. I now also know what Rorya meant by "container" control. I won't make that old mistake again; there will be plenty of new mistakes to make.

--Annie