VBA : Addressing array of text boxes

[RJSUK]

Hi all, I’m new to Excel VBA & hope someone can help me.

I’ve created an array (3*27) of Text boxes on a form using the following VBA code:
For Counter_Time = 0 To 26
For Counter_Lane = 0 To 2
Set Lanebox = Controls.Add("Forms.TextBox.1")
Lanebox.BorderStyle = 1
Lanebox.Left = 36 + (Counter_Lane * 162)
Lanebox.Top = 10 + ((Counter_Time - 3) * 15.25)
Lanebox.Width = 156
Lanebox.Height = 15
Next Counter_Lane
Next Counter_Time

I now need to access these text boxes when they are altered using the exit event. However I don’t know how to address all of them and which one of the array has been altered.

Can anyone help?
Thanks in anticipation!

[Derk]

Welcome to the OzGrid Forum!

It sounds like you want to capture the change event for a textbox. Each textbox will have its own, so you will need a macro for each one that takes the appropriate action when a change occurs. Alternatively you could run a loop and compare before and after text to see what changed, but then you would have to keep a separate record of contents for each textbox. I believe you would be better off with a macro for each textbox.

[RJSUK]

Derk,
Thanks for the reply!!!

With the approach you suggest I will need 81 subs 1 for each text box. I was rather hoping to find a method to have a single sub which would be able to address all 81 text boxes.

Do you think there is a way to do this?

Thanks again

RJSUK

[Derk]

As I mentioned before, you could loop through them all, and compare what each has now with what was there before.

The 81 subs can be very simple. They just need to call a common macro, passing the name of the textbox that was changed. The common macro would then do whatever was needed, so there would only be one macro to keep updated once you set up the 81 simple ones.

[RJSUK]

Thanks Derk,

Will give it a go

Cheers


RJSUK

[Andy Pope]

Hi RJSUK,

You can use a Class along with a holding object to capture SOME events.
You can not capture the Exit and Enter ones though.

This example will add the 81 textboxes, as per your code.
It will also output the textbox name and contents to the immediate window (CTRL+G in VBE)
Once the userform is displayed click it to add the textboxes.
Now type ABC in any of the boxes.
It should change the background colour to red. Now move to another textbox.
The previous one should return to white and the new one bacomes red.

'---Userform1 -------Userform Module------
Option Explicit

Private m_strLastName As String
Private ArrayTxt(81) As clsTxt
Public WithEvents EventTxt As MSForms.TextBox
Private Sub EventTxt_Change()
If m_strLastName <> EventTxt.Name And m_strLastName <> "" Then
Me.Controls(m_strLastName).BackColor = QBColor(15)
End If
m_strLastName = EventTxt.Name
Me.Controls(m_strLastName).BackColor = QBColor(12)
Debug.Print EventTxt.Name, EventTxt.Text
End Sub
Private Sub UserForm_Click()
Dim Counter_Time
Dim Counter_Lane
Dim LaneBox
Dim strName As String
Dim intIndex As Integer
For Counter_Time = 0 To 26
For Counter_Lane = 0 To 2
intIndex = intIndex + 1
strName = Format(Counter_Time, "00") & Format(Counter_Lane, "0")
Set LaneBox = Controls.Add("Forms.TextBox.1")
Set ArrayTxt(intIndex) = New clsTxt
Set ArrayTxt(intIndex).MyTxt = LaneBox
With ArrayTxt(intIndex)
With .MyTxt
.Name = strName
.BorderStyle = 1
.Left = 36 + (Counter_Lane * 162)
.Top = 10 + ((Counter_Time - 3) * 15.25)
.Width = 156
.Height = 15
End With
End With
Next Counter_Lane
Next Counter_Time
End Sub
&#039;--------------------------------------
&#039;----clsTxt-----------Class Module--
Option Explicit

Public LaneIndex
Public TimeIndex
Public WithEvents MyTxt As MSForms.TextBox
Private Sub MyTxt_Change()
Set UserForm1.EventTxt = Me.MyTxt
End Sub
&#039;-------------------

Depending on exactly what you are doing this technique may be of use.

Cheers
Andy

[Derk]

Wow! I like that Andy. However, i&#039;m a rank beginner with class modules and don&#039;t understand how
Set UserForm1.EventTxt = Me.MyTxt
does what it does. Can you please explain?
Thanks,
Derk

[RJSUK]

Andy,

That&#039;s great, Thanks a million

RJSUK

[Andy Pope]

Hi,

Here is some revised code, replacing the previous routines, that works on the first change.

&#039;----- userform
Public Property Set UseMyEvents(Txt As MSForms.TextBox)
&#039;
&#039; set the userform object to use the passed class instance Txt
&#039; This now allows capture of events
&#039;
Set Me.EventTxt = Txt

If m_strLastName <> EventTxt.Name And m_strLastName <> "" Then
Me.Controls(m_strLastName).BackColor = QBColor(15)
End If
m_strLastName = EventTxt.Name
Me.Controls(m_strLastName).BackColor = QBColor(12)
End Property
Private Sub EventTxt_Change()
Debug.Print EventTxt.Name, EventTxt.Text
End Sub

&#039;-----clsTxt
Private Sub MyTxt_Change()
&#039; Set the Textbox object in userform to use this instance of class object
Set UserForm1.UseMyEvents = Me.MyTxt
End Sub

I will post back an explanation as soon as I can write something that is meaningful

Cheers
Andy

[Andy Pope]

Explaining this is a lot harder than I thought.

Here is a previous thread that has some info regarding classes.
http://www.ozgrid.com/forum/viewthread.php?tid=8268

What I am doing is to set a reference through the single object in the userform, EventTxt to one of the arrayed objects.
This then allows for capture of the events within the userform.

This small routine demostrates how you can change the reference to an object.
&#039; Add to a code module

Sub Test() 
    Dim rngX As Range 
    Dim rngY As Range 
     
    Set rngX = ActiveSheet.Range("a1")  ' reference To A1 
    Set rngY = ActiveSheet.Range("a2")  ' reference To A2 
     
    rngX.Value = "Hello"  ' put text In A1 
    rngY.Value = "World"  ' put text In A2 
    MsgBox rngX.Value & " " & rngY.Value 
     
    Set rngY = rngX    ' reference To A1 
    MsgBox rngX.Value & " " & rngY.Value 
End Sub 

The last SET command points the object rngY to the same information as rngX

Hopefully this is a little clearer, although I wouldn&#039;t be suprised if my garbled explanation has left you even more

Cheers
Andy