Announcement

Collapse
No announcement yet.

VBA : Addressing array of text boxes

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA : Addressing array of text boxes

    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!

  • #2
    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.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      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

      Comment


      • #4
        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.
        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

        Comment


        • #5
          Thanks Derk,

          Will give it a go

          Cheers


          RJSUK

          Comment


          • #6
            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------
            Code:
            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
            '--------------------------------------
            '----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
            '-------------------

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

            Cheers
            Andy
            Last edited by royUK; May 28th, 2016, 15:45.

            Cheers
            Andy

            Comment


            • #7
              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
              Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

              Comment


              • #8
                Andy,

                That&#039;s great, Thanks a million

                RJSUK

                Comment


                • #9
                  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

                  Cheers
                  Andy

                  Comment


                  • #10
                    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
                    Code:
                    Sub Test&#40;&#41;
                        Dim rngX As Range
                        Dim rngY As Range
                        
                        Set rngX = ActiveSheet.Range&#40;"a1"&#41;  &#039; reference to A1
                        Set rngY = ActiveSheet.Range&#40;"a2"&#41;  &#039; reference to A2
                        
                        rngX.Value = "Hello"  &#039; put text in A1
                        rngY.Value = "World"  &#039; put text in A2
                        MsgBox rngX.Value & " " & rngY.Value
                        
                        Set rngY = rngX    &#039; 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

                    Cheers
                    Andy

                    Comment


                    • #11
                      Andy,

                      Thanks again that&#039;s even more help!

                      One problem left. I&#039;m now having difficulty picking up the use of a function key being pressed as Keyup and down don&#039;t seem to operate for this type of textbox or event.

                      Thanks again to all of you helping!!!!

                      RJSUK

                      Comment


                      • #12
                        This works for me. Placed in the userform code module.

                        Private Sub EventTxt_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
                        Select Case KeyCode
                        Case vbKeyF2
                        MsgBox "F2 was pressed"
                        Case vbKeyF3
                        MsgBox "F3 was pressed"
                        Case vbKeyF4
                        MsgBox "F4 was pressed"
                        End Select
                        End Sub

                        Cheers
                        Andy

                        Cheers
                        Andy

                        Comment


                        • #13
                          Andy,

                          Hertfordshire thanks Essex

                          Cheers

                          Ron

                          RJSUK

                          Comment


                          • #14
                            Andy,
                            Thanks for the explanation. I think I understand it now. The analogy with the range references helped; I hadn&#039;t realized the principle extended to events.
                            Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                            Comment


                            • #15
                              Guys,
                              Further to previous question and answer on Function key capture.
                              The code proposed by Andy works provided the text box is amended firstly by inputing another key code. e.g. "a" then "F12". If the first key pressed is the Function Key this event does not seem to be captured in the Keydown event.

                              Any ideas?

                              Thanks

                              RON

                              Comment

                              Working...
                              X