By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,127 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,127 IT Pros & Developers. It's quick & easy.

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

P: 2
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("A1: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.
Dec 21 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 635
Hi

You could try somethink like this
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim iRow As Long
  3.  
  4. Private Sub TextBox1_Change()
  5.     Cells(iRow + 1, 1) = TextBox1
  6.     iRow = iRow + 1
  7. 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
Dec 22 '09 #2

P: 2
@MikeTheBike
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!
Dec 23 '09 #3

Guido Geurs
Expert 100+
P: 767
dear,


I hope this will help

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton_Add_Click()
  2.    Range("A1").Select
  3.    Selection.SpecialCells(xlCellTypeLastCell).Select
  4.    Selection.Activate
  5.    ActiveCell.Offset(1, 0).Value = TextBox1.Text
  6. End Sub

br,
Dec 23 '09 #4

Expert 100+
P: 635
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!
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim iRow As Long
  3.  
  4. Private Sub TextBox1_AfterUpdate()
  5.  
  6.     Cells(iRow + 1, 1) = TextBox1
  7.     iRow = iRow + 1
  8.  
  9. End Sub
??

MTB
Dec 24 '09 #5

Guido Geurs
Expert 100+
P: 767
dear,

Maybe this will help You (see attachment)

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton_Add_Click()
  2.    Range("A1").End(xlDown).Activate
  3.    ActiveCell.Offset(1, 0).Value = TextBox1.Text
  4. End Sub
br,
Attached Files
File Type: zip add to last cell in range.zip (8.4 KB, 69 views)
Dec 24 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.