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

 

[ZVI]

Please clarify:
1. Do you mean that arrParts(1, 7) is for all textboxes?
2. Or should each textbox value be compared with different value of arrParts() with unique indexes?

 

[Annie Bender]

Please clarify:
1. Do you mean that arrParts(1, 7) is for all textboxes?
2. Or should each textbox value be compared with different value of arrParts() with unique indexes?

#2^^. The coding example I gave was for one specific example textbox, which corresponds to the value of array index 1,7. arrParts is dimensioned as (1 to 150, 1 to 9). Every textbox corresponding to arrParts(x,7) needs to be tested on exit. Those textboxes are all uniformly tagged.

--Annie

 

[ZVI]

Annie, add to VBAProject the modules/class modules as follows:
1. Module: Module1
2. Form: UserForm1
3. Class Module: clsWatchEvents

Place onto Userform1 a few TexBoxes for the testing, 3 are enough.

Code of Module1

' Code of Module1
Public Sub ShowForm()
  UserForm1.Show vbModeless
End Sub

Code of UserForm1

' Code of UserForm1
' Used the code of: Jaafar Tribak http://www.mrexcel.com/forum/showthread.php?t=519492
' Adapted by ZVI: http://www.mrexcel.com/forum/showthread.php?t=562713

Option Explicit
Option Compare Text
Const MYTAG = "MyTag"
Public WithEvents UserFormCtl As clsWatchEvents
Dim arrParts()

Private Sub UserForm_Initialize()
  ' Place your code here
  Dim c As MSForms.Control, i As Long
  ReDim arrParts(1 To 150, 1 To 9)
  ' The code below is just for test, use your code to fill arrParts
  For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
      i = i + 1
      ' Add tag to recognize the number (index) of Textbox,
      ' this can be done at design mode as well
      c.Tag = MYTAG & i
      ' Add test value to the textbox
      c.Object.Value = "MyData" & i
      ' Fill arrParts with value of TexBox
      arrParts(i, 7) = c.Object.Value
      'Debug.Print i, c.Tag, arrParts(i, 7)
    End If
  Next
End Sub

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, strTag As String
  
  ' Exit if Ctrl is not TextBox
  If Not TypeOf Ctrl Is MSForms.TextBox Then Exit Sub
  
  ' Exit if Tag of control is not like MYTAG
  strTag = Ctrl.Tag
  If Not Left(strTag, Len(MYTAG)) = MYTAG Then Exit Sub
  
  ' Calc the nimber of TextBox via its Tag (see Initialize code)
  i = Val(Mid(strTag, Len(MYTAG) + 1))
  
  ' 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

' === The code below are the same as in Jaafar Tribak example ===

Private Sub UserForm_Layout()
  If UserFormCtl Is Nothing Then
    Set UserFormCtl = New clsWatchEvents
    UserFormCtl.StartWatching Me
  End If
End Sub

Private Sub UserForm_Terminate()
  UserFormCtl.StopWatching
  Set UserFormCtl = Nothing
End Sub

Private Sub CommandButton1_Click()
  Unload Me
End Sub

Private Sub UserFormCtl_OnEnter(Ctrl As MSForms.Control)
  'Do nothing
End Sub

Code of clsWatchEvents

' Class module: clsWatchEvents
' Author: Jaafar Tribak http://www.mrexcel.com/forum/showthread.php?t=519492

Option Explicit

Public Event OnEnter(Ctrl As MSForms.Control)
Public Event OnExit(Ctrl As MSForms.Control, Cancel As Boolean)

Private bFormUnloaded As Boolean
Private bCancel As Boolean
Private oPrevActiveCtl As MSForms.Control

Public Sub StartWatching(Form As UserForm)
  bFormUnloaded = False
  Set oPrevActiveCtl = Form.ActiveControl
  RaiseEvent OnEnter(Form.ActiveControl)
  Do While bFormUnloaded = False
    If Not oPrevActiveCtl Is Nothing Then
      If Not oPrevActiveCtl Is Form.ActiveControl Then
        RaiseEvent OnExit(oPrevActiveCtl, bCancel)
        If bCancel Then
          oPrevActiveCtl.Enabled = False
          oPrevActiveCtl.Enabled = True
          oPrevActiveCtl.SetFocus
        Else
          RaiseEvent OnEnter(Form.ActiveControl)
          Form.ActiveControl.SetFocus
        End If
      End If
    End If
    Set oPrevActiveCtl = Form.ActiveControl
    bCancel = False
    DoEvents
  Loop
End Sub

Public Sub StopWatching()
  Set oPrevActiveCtl = Nothing
  bFormUnloaded = True
End Sub

 

[Annie Bender]

Thank you Vlad. I'm just now getting back to the project, so have not yet tried your suggestion, but I have read through it and at least I understand what you have done.

Another hour or so and I'll know if I have done this right. Thanks for all your effort.

--Annie

 

[Annie Bender]

Your sample code works beautifully. That's just exactly what I want.

BUT, when I try to adapt it to my form, the sub UserFormCtl_OnExit doesn't fire when I exit any of the textboxes, only when I exit the form itself. Obviously I am doing something wrong, but I cannot see what the problem is. Here's how the form is set up:

On initialize--
(1) the array arrParts() is dynamically redimmed to however many records (up to 150) there are in the associated inventory worksheet. No problem so far.

(2) the arrParts() is loaded with values from the inventory worksheet. The worksheet allows up to 150 rows, each of which has 9 data columns. Hence, the max redim of arrParts is (1 to 150, 1 to 9). No problem so far. I do this so that later, any edit to the 7th textbox of each of the 150 rows of textboxes can be compared back to the initialized value of arrParts(x,7) to detect the change and trigger the msgbox.

(3) I loop through arrParts 1 To UBound(arrParts) to assign the initial array values to the respective textboxes. No problem there.

(4) The form is displayed, ready for user edits. No problem with this either.

All edits made on the form can then be saved back to the associated inventory worksheet as updated values. But if any of those edits affects the value of the 7th textbox in each row, I want the user to immediately be asked if the change should decrement the inventory value (that's the 3rd textbox in each row). The class module is supposed to intercept that change only to the 7th textbox of any row of textboxes on the form.

Here is what I have modified on your suggested coding:

(1) the part that assigns initial test values to the array and to the textboxes. Those values come directly from the underlying worksheet.
(2) the part that assigns a unique tag to each textbox. All of the 7th textboxes have been assigned a common tag of "1" at design time. Then, in the sub UserFormCtl_OnExit, rather than testing for MYTAG & i, as in your suggested code, I test for Ctrl.Tag = 1. I do not think this is the problem, because this coding is in the OnExit sub that doesn't even fire until the form itself is closed. Maybe I'm wrong about that?

The class module itself I left identical to yours, as are UserForm_Layout(), UserForm_Terminate(), and UserFormCtl_OnEnter.

Should I just post all of the relevant coding? Thanks for reading through this long reply. You're a very patient man.

--Annie

 

[Annie Bender]

Some further testing shows me that the 'Form.ActiveControl' variable has no value when it's passed from the form to the class module (StartWatching sub). That, in turn, passes no value on to oPrevActiveCtl.

So, the line

RaiseEvent OnExit(oPrevActiveCtl, bCancel)

never gets triggered. I wonder why the active control is not recognized when textbox values are initialized by reading worksheet values directly into the textboxes? Could that be the problem?

--Annie

 

[ZVI]

Annie, it is required to associate each TextBox with array row of arrParts().
In my example the row number is in Tag, but in your code it is not and this is issue.

You’ve mentioned also dynamic textbox controls, what dynamic means?
How do you construct the form? There are two ways:
1. Manually in design mode
2. At runtime with adding controls via the code

Please post your code of UserForm to analize it.

Vlad

 

[Annie Bender]

Annie, it is required to associate each TextBox with array row of arrParts().
In my example the row number is in Tag, but in your code it is not and this is issue.

I'm sure that you are right, but I do not know how to "associate each TextBox with array row of arrParts()", other than to assign an array value to each textbox. I tried to work with your coding (below) but it overruns the array, because there are actually 1,200 textboxes in 150 rows of 8 textboxes each. Once "i" reaches 151, error.

For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
        i = i + 1
      ' Add tag to recognize the number (index) of Textbox,
      ' this can be done at design mode as well
      c.Tag = MYTAG & i
      ' Add test value to the textbox
      c.Object.Value = "MyData" & i
      ' Fill arrParts with value of TexBox
      arrParts(i, 7) = c.Object.Value
      ' Debug.Print i, c.Tag, arrParts(i, 7)
    End If
Next

You’ve mentioned also dynamic textbox controls, what dynamic means?
How do you construct the form? There are two ways:
1. Manually in design mode
2. At runtime with adding controls via the code

The form is entirely constructed in design mode, but only the first 10 rows of textboxes are visible at first. Once the user has entered inventory data into these 10 rows, as many additional rows as there is additional data become visible. That's what I meant by dynamic. In other words, the coding reads the number of records in the worksheet and redims arrParts to that size (plus 1 more for an empty row of textboxes.) But no new controls are created at run time.

Here is the userform coding:

Option Explicit
Option Compare Text
Const MYTAG = "MyTag"
Public WithEvents UserFormCtl As clsWatchEvents
Private bFormUnloaded As Boolean
Private bCancel As Boolean
Dim intLastRow As Integer
Dim arrParts()

Private Sub UserForm_Initialize()
Dim i As Integer, ii As Integer, iii As Integer, iv As Integer, intArrayDim As Integer, intNLA As Integer
Dim intTypesOnHand As Integer, intPcsOnHand As Integer, intBought As Integer, intInstalled As Integer
Dim dblTotalCost As Double, dblBoughtCost As Double, dblNLACost As Double
Dim c As MSForms.Control

Application.ScreenUpdating = False
Sheets("Replacement Parts").Select
intLastRow = Range("B155").End(xlUp).Row
If intLastRow = 6 Then
    intLastRow = 7
End If
intArrayDim = intLastRow - 6
Erase arrParts()
ReDim arrParts(1 To intArrayDim + 1, 1 To 8)
If intArrayDim = 150 Then
    ReDim arrParts(1 To intArrayDim, 1 To 8)
End If
If 1 + (20 * UBound(arrParts)) > 221 Then
    Frame1.ScrollHeight = 1 + (20 * UBound(arrParts))
End If
iii = 0
iv = 0

' ===== 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

' ===== Step 2: Calculate, format and display inventory data stats =====
  'various calculations and label format steps follow

Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Layout()
  If UserFormCtl Is Nothing Then
    Set UserFormCtl = New clsWatchEvents
    UserFormCtl.StartWatching Me
  End If
End Sub

Private Sub UserForm_Terminate()
  UserFormCtl.StopWatching
  Set UserFormCtl = Nothing
End Sub

Private Sub UserFormCtl_OnEnter(Ctrl As MSForms.Control)
    'Do nothing
End Sub

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, strTag As String
  
  ' Exit if Ctrl is not TextBox
  If Not TypeOf Ctrl Is MSForms.TextBox Then Exit Sub
  
  ' Exit if Tag of control is not 1, the tag given to the 7th textbox in each row
  If Not Ctrl.Tag = 1 Then Exit Sub
  
  ' Compare value of TextBox with arrParts(i,7)
  ' Oops, there is something missing here. The value of i
  If Ctrl.Object.Value <> arrParts(i, 7) Then
    Ctrl.BackColor = &HC0C0FF
    If MsgBox(WARNMSG, vbYesNo + vbDefaultButton2) = vbYes Then
      ' vbYes is choosen
      Ctrl.BackColor = &H80000005
      ' 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

Private Sub CommandButton1_Click()
Me.Hide
Erase arrParts()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim i As Integer, ii As Integer, iii As Integer, iv As Integer
iii = 0
iv = 0

' ===== user has clicked the "Save changes" button to update the parts inventory records. First do
'       validation tests to ensure good entries. If OK, write updated info to Parts worksheet =====
For i = 1 To UBound(arrParts)
    If Me.Controls("TextBox" & 1 + iv).Tag = 2 And Me.Controls("TextBox" & 1 + iv).Value = "" And _
    (Me.Controls("TextBox" & 1 + iv + 1).Value <> "" Or Me.Controls("TextBox" & 1 + iv + 2).Value <> "" Or _
    Me.Controls("TextBox" & 1 + iv + 3).Value <> "" Or Me.Controls("TextBox" & 1 + iv + 4).Value <> "" Or _
    Me.Controls("TextBox" & 1 + iv + 5).Value <> "" Or Me.Controls("TextBox" & 1 + iv + 6).Value <> "") Then
        MsgBox "There is missing data that must be provided", vbExclamation + vbOK, "Missing Data"
        Me.Controls("TextBox" & 1 + iv).SetFocus
        Me.Controls("TextBox" & 1 + iv).SelStart = 0
        Me.Controls("TextBox" & 1 + iv).SelLength = Len(Me.Controls("TextBox" & 1 + iv))
        Exit Sub
    ElseIf Me.Controls("TextBox" & 1 + iv).Tag = 2 And Me.Controls("TextBox" & 1 + iv).Value = "" Then
        Me.Controls("ComboBox" & i).Value = ""
    End If
    Range("B" & i + 6) = Me.Controls("TextBox" & 1 + iv).Value
    For ii = 2 To 7
        iii = 2 * (ii - 1)
        Range("B" & i + 6).Offset(0, iii) = Me.Controls("TextBox" & ii + iv).Value
    Next ii
    Range("B" & i + 6).Offset(0, iii + 2) = Me.Controls("ComboBox" & i).Value
    iv = iv + 7
Next i
MsgBox "Click OK to finish updating your replacement parts inventory", vbInformation + vbOKOnly, "One Step Remains"
End Sub

 

[Norie]

Annie

This is kind of off topic but I'll ask it anway.

Why do you need/want all these textboxes?

You could display the records in a listbox and add functionality to allow the user to add/delete/change records.

You would only really need one set if textboxes, where the user can enter new records and change existing records.

To delete records all they would need to do would select them in the listbox and click a 'Delete' button.

If you wanted you could add further functionality to, say, search for records.

There's another advantage - you would only have a few textboxes to validate.

 

[Annie Bender]

Hi Norie. Glad you looked in on this thread. I agree with you that that's a lot of textboxes, and is a bit unwieldy and very memory-hungary, so an alternate approach is welcome, if it gives me the functionality I want. Being new to VBA, I designed the form based on what I've done before and know a little bit about. Hence, the textboxes.

The key here is that each inventory item has 7 bits of information about it, and needs to be kept together on the userform display and in the worksheet, and needs to be added and/or deleted as a unit. (In earlier posts, I said 8 bits of info each, but it's now only 7.) And I want to be able to sort the inventory list by each of the 7 bits of info (item description, cost, purchase date, installation date, # of items on hand, etc.). Would a listbox give me that functionality?

I will have to do some small-scale testing of a listbox layout to familiarize myself with how that could work in this application. Any additional guidance you can offer would be much appreciated.

--Annie