I have in a userform a text box available for inputing text. Iam having
trouble with the following: I dont know how to make it so that the user can
input text in the textbox within Userform and have him/her click a command
button when done, and this text in the text box I want it to appear in a new
cell of a range named "R2".

Any ideas???

First, I don't think you'll be able to use a range named R2. It looks like the
address of a cell in A1 reference style--or it looks like the address of row 2
in R1C1 reference style.

And how do you define a new cell in that range--the first empty cell you find?

And how do you look through the range--by rows, by columns???

I changed the range name to _R2 in this sample.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range
Dim FoundIt As Boolean

Set myRng = Worksheets("sheet1").Range("_R2")

FoundIt = False
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
'found it
myCell.Value = Me.TextBox1.Value
FoundIt = True
Exit For
End If
Next myCell

If FoundIt = False Then
MsgBox "no ""new"" cells in range"
End If
End Sub

N.F wrote:

I have in a userform a text box available for inputing text. Iam having
trouble with the following: I dont know how to make it so that the user can
input text in the textbox within Userform and have him/her click a command
button when done, and this text in the text box I want it to appear in a new
cell of a range named "R2".

Any ideas???

--

Dave Peterson

Thank You Dave!!!
Deeply appreciate your help. I was spending hours on this same problem
before I had posted a question!!



"Dave Peterson" wrote:

First, I don't think you'll be able to use a range named R2. It looks like the
address of a cell in A1 reference style--or it looks like the address of row 2
in R1C1 reference style.

And how do you define a new cell in that range--the first empty cell you find?

And how do you look through the range--by rows, by columns???

I changed the range name to _R2 in this sample.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range
Dim FoundIt As Boolean

Set myRng = Worksheets("sheet1").Range("_R2")

FoundIt = False
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
'found it
myCell.Value = Me.TextBox1.Value
FoundIt = True
Exit For
End If
Next myCell

If FoundIt = False Then
MsgBox "no ""new"" cells in range"
End If
End Sub

N.F wrote:

I have in a userform a text box available for inputing text. Iam having
trouble with the following: I dont know how to make it so that the user can
input text in the textbox within Userform and have him/her click a command
button when done, and this text in the text box I want it to appear in a new
cell of a range named "R2".

Any ideas???

--

Dave Peterson

Just thought I would point out that you don't need to create/use the Boolean
'FoundIt' flag in your subroutine... this modification of your code will
work just as well:

Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("_R2")

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
myCell.Value = Me.TextBox1.Value
Exit For
End If
Next myCell

If TypeName(myCell) = "Nothing" Then
MsgBox "No ""new"" cells in range"
End If
End Sub

where the "Nothing" String constant must use the letter-casing shown. You
can also use the following alternate construction for that last If-Then
block...

If VarType(myCell) = vbObject Then
MsgBox "No ""new"" cells in range"
End If

but I don't consider it is as "self-documenting" as the one I actually used
inside the subroutine.

Rick



"Dave Peterson" wrote in message
...
First, I don't think you'll be able to use a range named R2. It looks
like the
address of a cell in A1 reference style--or it looks like the address of
row 2
in R1C1 reference style.

And how do you define a new cell in that range--the first empty cell you
find?

And how do you look through the range--by rows, by columns???

I changed the range name to _R2 in this sample.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range
Dim FoundIt As Boolean

Set myRng = Worksheets("sheet1").Range("_R2")

FoundIt = False
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
'found it
myCell.Value = Me.TextBox1.Value
FoundIt = True
Exit For
End If
Next myCell

If FoundIt = False Then
MsgBox "no ""new"" cells in range"
End If
End Sub

N.F wrote:

I have in a userform a text box available for inputing text. Iam having
trouble with the following: I dont know how to make it so that the user
can
input text in the textbox within Userform and have him/her click a
command
button when done, and this text in the text box I want it to appear in a
new
cell of a range named "R2".

Any ideas???

--

Dave Peterson

I like the boolean variable, but this would be an alternative, too:

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("_R2")

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
Exit For
End If
Next myCell

If isempty(mycell.value) Then
myCell.Value = Me.TextBox1.Value
else
MsgBox "No ""new"" cells in range"
End If

End Sub

But I'd still use the boolean variable.


"Rick Rothstein (MVP - VB)" wrote:

Just thought I would point out that you don't need to create/use the Boolean
'FoundIt' flag in your subroutine... this modification of your code will
work just as well:

Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("_R2")

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
myCell.Value = Me.TextBox1.Value
Exit For
End If
Next myCell

If TypeName(myCell) = "Nothing" Then
MsgBox "No ""new"" cells in range"
End If
End Sub

where the "Nothing" String constant must use the letter-casing shown. You
can also use the following alternate construction for that last If-Then
block...

If VarType(myCell) = vbObject Then
MsgBox "No ""new"" cells in range"
End If

but I don't consider it is as "self-documenting" as the one I actually used
inside the subroutine.

Rick

"Dave Peterson" wrote in message
...
First, I don't think you'll be able to use a range named R2. It looks
like the
address of a cell in A1 reference style--or it looks like the address of
row 2
in R1C1 reference style.

And how do you define a new cell in that range--the first empty cell you
find?

And how do you look through the range--by rows, by columns???

I changed the range name to _R2 in this sample.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim myRng As Range
Dim FoundIt As Boolean

Set myRng = Worksheets("sheet1").Range("_R2")

FoundIt = False
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
'found it
myCell.Value = Me.TextBox1.Value
FoundIt = True
Exit For
End If
Next myCell

If FoundIt = False Then
MsgBox "no ""new"" cells in range"
End If
End Sub

N.F wrote:

I have in a userform a text box available for inputing text. Iam having
trouble with the following: I dont know how to make it so that the user
can
input text in the textbox within Userform and have him/her click a
command
button when done, and this text in the text box I want it to appear in a
new
cell of a range named "R2".

Any ideas???

--

Dave Peterson

--

Dave Peterson