Input from TextBox on UserForm

[Kev]

I wish to take an entry from a textbox on a userform
(text only), and add it to a list of customer names all
in one column on another worksheet. Unsure of how to get
data as a string and then add to the other sheet.

In adding to the other sheet should I look for the last
cell in the column with text in it and add data to the
next cell down. Some hints on how to achieve this please.

OR select the sheet and then a row in the range, insert
row, then select vacant cell and paste my data into that
cell?

Thanks in advance....
Kev

 

[Bob Phillips]

With Worksheet("Sheet1")
cLastRow = .Cells(Rows.Count,"A").End(xlUp).Row
.Range("A" & cLastRow+1).Value = Userform1.Textbox1.Text
End With

make sure the form is still in memory when you do this, that is don't unload
it.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Kev]

Thanks for quick response.
How do I then make these new entries part of an existing
range ("Customers") ie. a statement to redefine the range
size to inlude new data???

Thanks again......Kev.

 

[Bob Phillips]

Something like

With Worksheet("Sheet1")
cLastRow = .Cells(Rows.Count,"A").End(xlUp).Row
.Range("A" & cLastRow+1).Value = Userform1.Textbox1.Text
.Range("Customers").Cells(1,1).Resize(11,Range("Customers"). _
columns.count).Name = "Customers"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Kev]

Thanks again Bob, BUT...
the resizing of the range seems to be causing some issues
ie. use this range in a drop down list and not all names
appear. When I go back and look at the range cell
references, they do not include the complete customer
list. Defined the range originally on a worksheet not
through VBA - could this be causing problems. Or if this
range is referred to in different modules??
Again I thank you for your input and time....
Kev.

 

[Ken Macksey]

Hi

Modified Bob's code slightly and this seemed to work for me.

With Worksheets("Sheet1")

clastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & clastrow + 1).Value = UserForm1.TextBox1.Text
.Range("Customers").Cells(1, 1).Resize(clastrow + 1,
Range("Customers"). _
Columns.Count).Name = "Customers"

End With

HTH

Ken

 

[Bob Phillips]

Kev,

The fact that you originally defined it in a worksheet should make no
difference, nor should where it is used.

I think I left testdata in the code. Try this

Dim cLastRow As Long
With Worksheets("Sheet1")
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & cLastRow + 1).Value = "abc" 'UserForm1.Textbox1.Text
With .Range("Customers")
.Cells(1, 1).Resize(.Rows.Count + 1, _
.Columns.Count).Name = "Customers"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

oops, did it again

Dim cLastRow As Long
With Worksheets("Sheet1")
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & cLastRow + 1).Value = UserForm1.Textbox1.Text
With .Range("Customers")
.Cells(1, 1).Resize(.Rows.Count + 1, _
.Columns.Count).Name = "Customers"
End With
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)