Userforms and textbox

watkins6878

Board Regular
Joined
Nov 26, 2005
Messages
96
Hi All

The problem i am having is this: I have a Workbook with two worksheets
Sheet1 has 6 columns (Delivery NO,Customer,Store,order No,size,Drops
on this sheet i run a macro to find a delivery No and display its location, I would like to be able if possible to copy the contents of the row then have a userform which i enter a load No and Drop No in and then paste this to the next empty line in sheet two which has 8 columns in

Load No,Drop No,Delivery NO,Customer,Store,order No,size,Drops
Any tip would be helpful

thanks in advance
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello watkins6878,
I'm not quite clear on exactly what you want to do.
It seems you already have the code to find the location (row to copy?) of a Delivery # but I'm unclear on what you want to do at this point.
Do you want to copy this row and then load up a userform with (I assume) a couple textboxes that you can enter the Load # & Drop # in, and then paste what into sheet2?
 

watkins6878

Board Regular
Joined
Nov 26, 2005
Messages
96
I want to copy the row then in my user form i have two textboxs which i will enter a load No and Drop No then i want to paste the rowwhich was copied earlier and the infromation from the userform into sheet 2
The userform will relate to columns a & b then the copied row will take the next 6 columns
but this may need to be done 100 times so it would need to look for the next empty line in sheet 2
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
OK, this can be done in two steps.
When your existing code copies the row of interest, make your destination be: Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp)(2, 1)
That will take care of the columns C through H.
You can then show your userform. Once you get your values entered into the textboxes, the code to paste them to columns A & B would look like this:
Code:
With Sheets("Sheet2")
  'Put TextBox1 value into columnA
  .Cells(Rows.Count, "C").End(xlUp).Offset(, -2).Value = TextBox1.Text
  'Put TextBox2 value into columnB
  .Cells(Rows.Count, "C").End(xlUp).Offset(, -1).Value = TextBox2.Text
End With
Does that help?
 

watkins6878

Board Regular
Joined
Nov 26, 2005
Messages
96

ADVERTISEMENT

the first macro uses an inputbox to enter delivery no could i user another text box on my user form instead and if so how would i need to change this code

Dim Target As Range, FirstAddress As String, It As String
It = InputBox("Find what?", "Looking For?")
If It = Empty Then Exit Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
OK. I'm only thinking that this is what you're attempting to do, so let me explain what I've done here.
Say you have your userform with 3 textboxes and a command button.
TextBox1 is for the Load #.
TextBox2 is for the Drop #.
TextBox3 is for the value to search for. (The Delivery #.)

Assumming your data is layed out as I understand it...

(Sheet1 contains):
Delivery #, Customer, Store, Order #, Size & Drops in columns A through F respectively.

(Sheet2 contains):
Load #, Drop #, Delivery #, Customer, Store, Order #, Size & Drops in columns A through H respectively.

Then this code for your userform commandbutton should do what you're after.
Code:
Private Sub CommandButton1_Click()
Dim fCell As Range, fCellRow As Long
If TextBox3 <> "" Then
    Set fCell = Cells.Find(TextBox3, lookat:=xlWhole)
    If Not fCell Is Nothing Then
        fCellRow = fCell.Row
        With Sheets("Sheet2")
            .Cells(Rows.Count, "A").End(xlUp)(2, 1).Value = TextBox1.Text
            .Cells(Rows.Count, "B").End(xlUp)(2, 1).Value = TextBox2.Text
        End With
        Sheets("Sheet1").Range(Cells(fCellRow, 1), Cells(fCellRow, 6)).Copy _
            Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp)(2, 1)
    End If
End If
End Sub
Am I even close to what you're looking for?

[EDIT:]
Oh yeah, this also assumes you'll be showing the userform (& therefore running the code) while sheet1 is the activesheet...
 

watkins6878

Board Regular
Joined
Nov 26, 2005
Messages
96

ADVERTISEMENT

Yes works great thankyou very much easy when you know how you sorted that in half the time and with nowhere near as much code as i was trying to use

also would it be much work if i want to stop duplicate delivery No from being pasted into sheet2
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
watkins6878 said:
also would it be much work if i want to stop duplicate delivery No from being pasted into sheet2
Nope, not much work at all.
Try replacing the commandbutton code with this...
Code:
Private Sub CommandButton1_Click()
Dim fCell As Range, fCellExists As Range, fCellRow As Long
If TextBox3 <> "" Then
  Set fCell = Cells.Find(TextBox3.Text, lookat:=xlWhole)
  If Not fCell Is Nothing Then
    fCellRow = fCell.Row
    With Sheets("Sheet2")
      Set fCellExists = .Columns(3).Find(fCell, lookat:=xlWhole)
      If Not fCellExists Is Nothing Then _
        MsgBox ("Delivery number '" & fCell.Value & "' already exists in sheet 2."), , _
        "Duplicate Delivery Number...": Exit Sub
      .Cells(Rows.Count, "A").End(xlUp)(2, 1).Value = TextBox1.Text
      .Cells(Rows.Count, "B").End(xlUp)(2, 1).Value = TextBox2.Text
    End With
    Sheets("Sheet1").Range(Cells(fCellRow, 1), Cells(fCellRow, 6)).Copy _
        Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp)(2, 1)
  End If
End If
End Sub
That any better?
 

watkins6878

Board Regular
Joined
Nov 26, 2005
Messages
96
sorry about this i have one other thing to ask if possible with the data in sheet2 I would like to sort the data by load number and Drop order which i have done with a sort macro but is there a way to have a macro look for the last instance of say load 1 then insert a blank row and then load 2 then insert a blank row and on Etc Etc
load are displayed with numbers only
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,109
Members
412,441
Latest member
kelethymos
Top