data transfer to worksheet

monty toler

New Member
Joined
Jan 27, 2005
Messages
4
:rolleyes: Mr excel,

iam tring to send data from a vbuserform (textbox) to a xlworksheet which is no problem, but when i send the next set of data it overwrites the first. I need the second & third and all other entrys to move down to the next row. I have requested this before so if you have allready recieved this question disregard this one iam not sure if i send the first one the right way. Iam a novice so any help would be appreciated.


thanks monty Toler
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Here's a method for moving date to the next empty row:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> LastRow = Range("A65536").End(xlUp)
        
        <SPAN style="color:#00007F">With</SPAN> LastRow
            .Offset(1, 0) = TextBox1
            .Offset(1, 1) = TextBox2
            .Offset(1, 2) = TextBox3
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 

monty toler

New Member
Joined
Jan 27, 2005
Messages
4
thanks smitty i'll let know if i have any more problems which iam sure i will.


If their is any books you would recommend for a beginner please let know because i dont have anyone to ask question
or turn to help.


thanks
monty toler
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
John Walkenbach's Excel Power Programming with VBA is a great resource. You can check it out and get some good VBA tips at www.j-walk.com.

Chip Pearson also has good tips at www.cpearson.com and www.vbaexpress.com is a good place for VBA questions, but you can almost always get a question answered here.

Smitty
 

monty toler

New Member
Joined
Jan 27, 2005
Messages
4

ADVERTISEMENT

reset

smitty,
thanks for the help on moving to the next row just what i needed
i have another problem after entering data from A1 to A31 i need to reset or start back at A1 i can't seem to acheive this could you help me ?



thanks
monty toler
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post the code that you've got so far?

I imagine you could use a change event to reset once you get to A32 but you'd need to give some specifics as to what's going to cause the reset.

Smitty
 

monty toler

New Member
Joined
Jan 27, 2005
Messages
4
reset

Smitty sorry i should have posted the code the first time.
Dim data as interger
data = textbox1
Dim lastrow as object
Set lastrow = Range("A31").End(xlup)
with lastrow
.offset(1,0) = data
End With


thanks
Monty Toler
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
How about adapting pennysaver's code:
Code:
Private Sub CommandButton1_Click()
Dim NextRow As Long

        NextRow = Range("A65536").End(xlUp).Row+1
        
        If NextRow = 32 Then NextRow = 1
        
        Range("A" & NextRow) = textbox1.Value               
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,685
Messages
5,597,531
Members
414,152
Latest member
ReservoirDodds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top