Textbox value insert in two cells

[kk]

How do modify the following code below so that the value in texbox1 will also
be inserted in sheet5 cell "A1"

Set LastRow = Sheet2.Range("a100").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text

 

[Dave Peterson]

sheet5.range("a1").value = textbox1.text

(where sheet5 is the code name for that worksheet.)

 

[Bob Phillips]

Set LastRow = Sheet2.Range("a100").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
Wrksheeys("Sheet5").Range("A1").Value = TextBox1.Text


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

No idea what happened to my typing, it should be

Set LastRow = Sheet2.Range("a100").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
Worksheets("Sheet5").Range("A1").Value = TextBox1.Text


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[kk]

Thanks guys
But I need sheet5 to be the same as sheet2.
In other words it looks for the last empty row both on sheet2 and sheet5 and
inserts textbox1 value.

 

[kk]

Sorry about that I should have made it clear at first. I was meant to say
last empty row, instead of inserting in cell A1.
sorry!!!!!
--
kk

Thanks guys
But I need sheet5 to be the same as sheet2.
In other words it looks for the last empty row both on sheet2 and sheet5 and
inserts textbox1 value.

 

[Dave Peterson]

The same thing as you did before:

Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text

Make sure that 100 is large enough!

Thanks guys
But I need sheet5 to be the same as sheet2.
In other words it looks for the last empty row both on sheet2 and sheet5 and
inserts textbox1 value.

 

[kk]

Thanks Dave,
For some reason it does not work!
This is part of the code I have. Any help would be great!

Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object
Application.EnableEvents = False

Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text

If vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""

Else
End If

Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox3.Text
LastRow.Offset(1, 1).Value = TextBox4.Text

MsgBox "Do you want to enter another record?", vbYesNo

If vbYes Then
TextBox3.Text = ""
TextBox4.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""

TextBox1.SetFocus

Application.EnableEvents = True

Call CommandButton99_Click

Else
End If
End Sub

 

[Dave Peterson]

I have no idea what your real purpose is, but maybe this does what you want:

Option Explicit
Private Sub CommandButton1_Click()

Dim LastRow As Range
Dim Resp As Long

Application.EnableEvents = False

Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text

Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox3.Text
LastRow.Offset(1, 1).Value = TextBox4.Text


Resp = MsgBox(Prompt:="Do you want to enter another record?", _
Buttons:=vbYesNo)

If Resp = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""

TextBox1.SetFocus
Call CommandButton99_Click

End If

Application.EnableEvents = True

End Sub

 

[kk]

Here we go I shall try to explain it!!
I have a userform with several textboxes and comboboxes.
Textbox1 is titled �Name�.
New users enter their names in textbox1 and then fill in all the other
textboxes. The data is saved in sheets 2 in the next empty row, with the
names in column �A�.
I have run out of columns in sheet2 so I have added a new sheet (sheet5)
with the same headings as sheet2.
How do I get the name from textbox1 to load in sheet5 in the next empty row.
The same as sheet2?

 

[Dave Peterson]

Why did your code use textbox3 and textbox4?

Change them to what you want.

Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox3.Text
LastRow.Offset(1, 1).Value = TextBox4.Text

Here we go I shall try to explain it!!
I have a userform with several textboxes and comboboxes.
Textbox1 is titled �Name�.
New users enter their names in textbox1 and then fill in all the other
textboxes. The data is saved in sheets 2 in the next empty row, with the
names in column �A�.
I have run out of columns in sheet2 so I have added a new sheet (sheet5)
with the same headings as sheet2.
How do I get the name from textbox1 to load in sheet5 in the next empty row.
The same as sheet2?

 

[kk]

I have manage to sort it now.


I had inserted
TextBox1.Text = ""
In the wrong place. It should have gone after the message box. In effect I
was clearing textbox1 before the data was entered in sheet5.

It all works fine now.

--
kk

Here we go I shall try to explain it!!
I have a userform with several textboxes and comboboxes.
Textbox1 is titled �Name�.
New users enter their names in textbox1 and then fill in all the other
textboxes. The data is saved in sheets 2 in the next empty row, with the
names in column �A�.
I have run out of columns in sheet2 so I have added a new sheet (sheet5)
with the same headings as sheet2.
How do I get the name from textbox1 to load in sheet5 in the next empty row.
The same as sheet2?