locked
Repetitive txtboxes RRS feed

  • Question

  • I have 20 Text boxes on a form (V1 to V20). I want this same routine for all. Is there a way somehow of writing one "_AfterUpdate" sub? 

    Private Sub V1_AfterUpdate()
    Dim MyRow As Integer
    MyRow = Sheet5.Cells(8, 2)
    Sheet3.Cells(MyRow, 8) = Val(V1)
    End Sub

    Wednesday, August 3, 2011 9:14 PM

Answers

  • Yes, It is possible even though, Excel VBA doesn't have the option of using Array Controls.

    Ok this is little bit advanced than the basic level, so please read it carefully.

    In your VBA project, insert a class module (Class1 will be the default name if there is no other Class with the same name) and then paste this code.

     

    Public WithEvents TextBoxEvents As MSForms.TextBox
    Public Index As Long
    
    Private Sub TextBoxEvents_Change()
     Dim MyRow As Integer
     MyRow = Sheet5.Cells(8, 2)
     Sheet3.Cells(MyRow, Index + 7) = Val(TextBoxEvents.Text)
    End Sub
    

     

    Now in your userform code area, paste this code.

     

    Dim TextArray() As New Class1
    
    Private Sub UserForm_Initialize()
     Dim i As Integer, TBCtl As Control
     Dim J As Long
     
     J = 1
     
     For Each TBCtl In Me.Controls
     If TypeOf TBCtl Is MSForms.TextBox Then
      i = i + 1
      ReDim Preserve TextArray(1 To i)
      Set TextArray(i).TextBoxEvents = TBCtl
      TextArray(i).Index = J
      J = J + 1
     End If
     Next TBCtl
     Set TBCtl = Nothing
    End Sub
    

     

    Now every time you type a value in the textbox, it will update Sheet3.

    Let me know if you face any difficulties :)


    Sid (A good exercise for the Heart is to bend down and help another up)

    Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.


    • Marked as answer by Macy Dong Thursday, August 11, 2011 2:21 AM
    Wednesday, August 3, 2011 10:36 PM

All replies

  • It's difficult for an _AfterUpdate() Sub. Would a _Change() Sub do?

    It would have the same effect though.

    Edit:

    One question though. Are all text boxes supposed to write to the same cell?


    Sid (A good exercise for the Heart is to bend down and help another up)

    Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.


    Wednesday, August 3, 2011 9:52 PM
  • They would write to different cells (i.e. Sheet3.Cells(MyRow, V# + 7)

    AfterUpdate vs Change  vs any other command wouldn't change the fact that I want to only write one (if possible).

    Wednesday, August 3, 2011 10:24 PM
  • Yes, It is possible even though, Excel VBA doesn't have the option of using Array Controls.

    Ok this is little bit advanced than the basic level, so please read it carefully.

    In your VBA project, insert a class module (Class1 will be the default name if there is no other Class with the same name) and then paste this code.

     

    Public WithEvents TextBoxEvents As MSForms.TextBox
    Public Index As Long
    
    Private Sub TextBoxEvents_Change()
     Dim MyRow As Integer
     MyRow = Sheet5.Cells(8, 2)
     Sheet3.Cells(MyRow, Index + 7) = Val(TextBoxEvents.Text)
    End Sub
    

     

    Now in your userform code area, paste this code.

     

    Dim TextArray() As New Class1
    
    Private Sub UserForm_Initialize()
     Dim i As Integer, TBCtl As Control
     Dim J As Long
     
     J = 1
     
     For Each TBCtl In Me.Controls
     If TypeOf TBCtl Is MSForms.TextBox Then
      i = i + 1
      ReDim Preserve TextArray(1 To i)
      Set TextArray(i).TextBoxEvents = TBCtl
      TextArray(i).Index = J
      J = J + 1
     End If
     Next TBCtl
     Set TBCtl = Nothing
    End Sub
    

     

    Now every time you type a value in the textbox, it will update Sheet3.

    Let me know if you face any difficulties :)


    Sid (A good exercise for the Heart is to bend down and help another up)

    Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.


    • Marked as answer by Macy Dong Thursday, August 11, 2011 2:21 AM
    Wednesday, August 3, 2011 10:36 PM