PDA

View Full Version : Solved: input data to post in worksheet



supermadin
09-05-2007, 04:07 AM
hi...


i made a textbox and i cannot put it in my worksheet...

to make it simple i want to accept the data that's inputed in the textbox and paste it on my worksheet...

thanks

rory
09-05-2007, 04:53 AM
Assuming it's on a userform, you can link the textbox to a specific cell, or use code like:
Activesheet.Range("A1").Value = Me.Textbox1.Text

supermadin
09-05-2007, 06:35 AM
thanks for the reply...

i have this code i don't know where to put that code...

i've tried to put in data like number or name but it doesn't work the way i want it..

please somebody help




Private Sub CommandButton1_Click()

Dim vTime As Date, vNumber As String, i As Long, j As Long, k As Long


Randomize
vNumber = Format(Int(Rnd * 1629 + 1), "0000")


For i = 1 To 4
vTime = Now
Do Until Now > vTime + TimeValue("00:00:01")
Randomize
If i = 1 Then
TextBox1.Text = CStr(Int(Rnd * 2))
k = 2
Else
k = i
End If
For j = k To 4
Me.Controls("TextBox" & CStr(j)).Text = CStr(Int(Rnd * 10))
Next
DoEvents
Loop
Me.Controls("TextBox" & CStr(i)).Text = Mid(vNumber, i, 1)
Next

UserForm1.TextBox5.Visible = True


Sheet1.Cells(Rows.Count, "A").End(xlUp)(2, 1) = TextBox1.Text
Sheet1.Cells(Rows.Count, "B").End(xlUp)(2, 1) = TextBox2.Text
Sheet1.Cells(Rows.Count, "C").End(xlUp)(2, 1) = TextBox3.Text
Sheet1.Cells(Rows.Count, "D").End(xlUp)(2, 1) = TextBox4.Text
Sheet1.Cells(Rows.Count, "E").End(xlUp)(2, 1) = Me.TextBox5.Text


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub Label3_Click()

End Sub

Private Sub TextBox5_Change()

End Sub

rory
09-05-2007, 06:47 AM
it doesn't work the way i want it
doesn't help us much. What is it doing and what do you want it to do? The code you have is already putting the textbox values onto Sheet1.

supermadin
09-05-2007, 06:54 AM
you are right i know it should put the text onto sheet1.

the textbox 1 to 4 is doing great thanks to matt... but when i added textbox 5 it doesn't paste or send the message that you put on textbox5 to sheet 1.

i don't know where did i go wrong..

thanks

mvidas
09-05-2007, 07:06 AM
Hi Marcy,

The code that does it is fine; perhaps the cell it is being added to is further up in column E? If columns A:D go to row 20 but column E only goes to row 5, you'll have inconsistent rows.

A way around that is to put them all in on the same row (rather than using End(xlup) for each column): Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 5).Value = _
Array(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text)Give that a try, see how it works out.


EDIT/ADDED: I just noted that you're only making textbox5 visible while creating the random number then immediately putting it on the sheet (without giving the user a chance to enter anything). You may want to put textbox5's text into column E when you unload the userform (looks like commandbutton2_click):Sheet1.Cells(Rows.Count, "D").End(xlUp).Offset(0, 1).Value = TextBox5.Text

supermadin
09-05-2007, 08:00 AM
Again a big thanks to Matt...

At first i didn't achive what i want to do but then i try to experiment on it and yes now it's working the way i want it...

big thanks to you guys especially Matt :clap2::clap2::clap2: :yay:yay:yay

mvidas
09-05-2007, 08:02 AM
What did you end up using to get it to work?

supermadin
09-05-2007, 08:14 AM
i've try this code.. at first i put it with the other code that put them in the worksheet but then when your run it you it will not post the first data so i try to put it inside textbox 5 and it work...

and i also hide my textbox when you start to generate again..

thanks matt


Sheet1.Cells(Rows.Count, "D").End(xlUp).Offset(0, 1).Value = TextBox5.Text