Transfer data to worksheet

[rharri1972]

Hello!
I have a userform "Cust_Line_Order_Form" with 4 textboxes. Textbox 13,4,&5, and a command button "OK".

When I click the "OK" button I am trying to get the textbox data to worksheet "CUSTOMER ORDER".

The first possible cell is B and row is 12.

B12 (Textbox1), C12(Textbox3), D12 (Textbox4), G12(Textbox5).

I've tried using the .end(xlup) with range ("B12:B") but nothing is happening on the execution of subroutine from button click.

I am using the (xlup) because there may be multiple line items.... each time i hit the ok button i need it to find the next available cell/row to enter the next data starting with B12.

Private Sub CommandButton1_Click()
Dim lr As Long
lr = Sheets("CUSTOMER ORDER").Cells(Rows.Count, "B").End(xlUp).Row + 1
 
   ActiveCell.Value = Me.TextBox1.Value
  
     ActiveCell.Offset(0, 1).Value = Me.TextBox3.Value
    
        ActiveCell.Offset(0, 2).Value = Me.TextBox4.Value

            ActiveCell.Offset(0, 3).Value = Me.TextBox5.Value
          

End Sub

                        any help will be greatly appreciated!!!

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Private Sub CommandButton1_Click()
    Dim lr              As Long
    Dim wsCustomerOrder As Worksheet
    
    Set wsCustomerOrder = ThisWorkbook.Worksheets("CUSTOMER ORDER")
    
    With wsCustomerOrder
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        If lr < 12 Then lr = 12
        
        .Cells(lr, 2).Value = Me.TextBox1.Value
        .Cells(lr, 3).Value = Me.TextBox3.Value
        .Cells(lr, 4).Value = Me.TextBox4.Value
        .Cells(lr, 7).Value = Me.TextBox5.Value
        
    End With
    
End Sub

Dave

 

[rharri1972]

Hi,
try this update to your code & see if does what you want

Private Sub CommandButton1_Click()
    Dim lr              As Long
    Dim wsCustomerOrder As Worksheet
   
    Set wsCustomerOrder = ThisWorkbook.Worksheets("CUSTOMER ORDER")
   
    With wsCustomerOrder
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        If lr < 12 Then lr = 12
       
        .Cells(lr, 2).Value = Me.TextBox1.Value
        .Cells(lr, 3).Value = Me.TextBox3.Value
        .Cells(lr, 4).Value = Me.TextBox4.Value
        .Cells(lr, 7).Value = Me.TextBox5.Value
       
    End With
   
End Sub

Dave

works great!! I also tried declaring variables... im just not there yet with the knowledge. Long is just to be able to handle more data or find more data right?

 

[dmt32]

The total number of rows on a worksheet versions from 2007 > is 1,048,576
so you declare A Long variable which can store a value up to 2,147,483,64

You may find this helpful:VBA for Beginners: VBA Variables Explained Simply - Online PC Learning

 

[rharri1972]

The total number of rows on a worksheet versions from 2007 > is 1,048,576
so you declare A Long variable which can store a value up to 2,147,483,64

You may find this helpful:VBA for Beginners: VBA Variables Explained Simply - Online PC Learning

thanks Dave!!

 

[rharri1972]

The total number of rows on a worksheet versions from 2007 > is 1,048,576
so you declare A Long variable which can store a value up to 2,147,483,64

You may find this helpful:VBA for Beginners: VBA Variables Explained Simply - Online PC Learning

Dave, I have posted this to the site, i wanted to let you know before sending this. I do not know what you guys can do or not do...like rules you may have. However, is it possible to start typing into a textbox and have it immediately start showing the possible matches from a listbox while i am typing? Listbox is on the same userform as the textbox.

 

[dmt32]

simple answer yes but its an unrelated question & should be answered in your other thread

Dave