Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: VBA : Addressing array of text boxes

  1. #1
    Join Date
    16th March 2004
    Posts
    30
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,236
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th March 2004
    Posts
    30
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,236
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th March 2004
    Posts
    30
    Thanks Derk,

    Will give it a go

    Cheers


    RJSUK

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,236
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th March 2004
    Posts
    30
    Andy,

    That&#039;s great, Thanks a million

    RJSUK

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314
    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314
    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
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 9
    Last Post: July 8th, 2005, 22:24
  2. Replies: 3
    Last Post: June 12th, 2004, 19:40
  3. Replies: 6
    Last Post: April 4th, 2003, 21:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno