Excel VBA 2003 : Populate Text on a range in sequence automatically

[jazznojive]

I've created a textbox control on a userform and I am trying to figure out how to make the text populate to a worksheet range in sequence automatically. I've figued out how to get the text that the user types in to populate in a specific range, but I'd like each entry from the user to populate on the next cell below the previous entry automatically. I've used .showdataform to do this, but I want the userform to be customized rather than using .showdataform. This is what I've been working with so far:

Private Sub TextBox1_Change ()
Dim R As Range
Dim S As String

Set R = Sheet1.Range("A 1:B10")
S = MyForm.Textbox1 .Value

R.Value = S

End Sub

Rather than having the range as A1 to B10 I'd like the text to populate in a range such as Column A and each entry from the user appear in cell A1, then A2, then A3 and so on.

[MikeTheBike]

Hi

You could try somethink like this

Option Explicit
Dim iRow As Long

Private Sub TextBox1_Change()
    Cells(iRow + 1, 1) = TextBox1
    iRow = iRow + 1
End Sub

I'm sure this isn't the complete solution for you (for instance not sure when/where/how to reset the row counter), but it may help?


MTB

[jazznojive]

Hi

You could try somethink like this

Option Explicit
Dim iRow As Long

Private Sub TextBox1_Change()
    Cells(iRow + 1, 1) = TextBox1
    iRow = iRow + 1
End Sub

I'm sure this isn't the complete solution for you (for instance not sure when/where/how to reset the row counter), but it may help?


MTB

Yes that's very close to what I'm looking for, but that solution results in a new cell choice for each character keyed in rather than for the complete textbox entry keyed in by the user. Any ideas on how to correct this?

Thank you!

[Guido Geurs]

dear,


I hope this will help

Private Sub CommandButton_Add_Click()
   Range("A1").Select
   Selection.SpecialCells(xlCellTypeLastCell).Select
   Selection.Activate
   ActiveCell.Offset(1, 0).Value = TextBox1.Text
End Sub

br,

[MikeTheBike]

Hi again

To do the job properly (ie a complete solution) we would need more info on the app, but this will be a little closed!

Option Explicit
Dim iRow As Long

Private Sub TextBox1_AfterUpdate()

    Cells(iRow + 1, 1) = TextBox1
    iRow = iRow + 1

End Sub

??

MTB

[Guido Geurs]

dear,

Maybe this will help You (see attachment)

Private Sub CommandButton_Add_Click()
   Range("A1").End(xlDown).Activate
   ActiveCell.Offset(1, 0).Value = TextBox1.Text
End Sub

br,