Recall Data to Userform

[Emoncada]

I have a userform that has about 80+ txtboxes that is manually entered and then is sent to a spreadsheet. That Form is then printed and clears. I need to know if I from the data saved on the spreadsheet can recall the form and auto populate the cells the way they were?. SO my goal is possibly have a txtbox or cell on the spreadsheet so if the form needs to be recalled they can enter the order number it will look at the data on the spreadsheet look in column A to find a match and if found pull all the data back to the form.

Can This be done ?

I have a form that can do this but the major difference is that form the data sent to the spreadsheet all goes into one row on the spreadsheet. So an order will all be in Row 11 for example.

In the case of this Userform I am working on has data for one order on multiple rows.

Any help would be great. Thanks.

[gwkenny]

If you populate the the worksheet by data from the form, it should be possible to backswords engineer populating the form by data from the worksheet.

Even if it is on multiple rows, as long as a standard logic (procedure) can be applied then it should be possible.

Unfortunately, without examples and code, it is hard to help you

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[Emoncada]

This is my cmdPrintSave button code that sends the form data to the spreadsheet.
[vba]Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String
Dim mpFind As Long

mpLookup = Me.TxtOrdNum.Text
On Error Resume Next
mpFind = Application.Match(mpLookup, Worksheets("Packing Slip Pim").Columns(1), 0)
On Error GoTo 0
If mpFind = 0 Then

InsertEm
Else

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

With Worksheets("Packing Slip Pim").Columns(1)

Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address

Do

Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then

Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With
InsertEm
If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
End Sub

Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
For i = 1 To 18
If Me.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
FrmPrint.Show
End Sub[/vba]

Got some help with this code from this forum

Hopefully that helps

[mikerickson]

Sub OuterPrintRoutine()
Dim RecallArray As Variant

With ThisWorkbook.Sheets("sheet1")
    RecallArray = Range(.UsedRange, .Range("A1")).Value
End With

Call printRoutine

Sheets("sheet1").Cells.ClearContents
Sheets("sheet1").Range("a1").Resize(UBound(RecallArray, 1), UBound(RecallArray, 2)).Value = RecallArray

End Sub

[Emoncada]

so in your code a1 would have the value that is manually entered?

[Emoncada]

That is giving me a compile error sub or function Not Defined.
Hightlights Call PrintRoutine

[mikerickson]

The A1 is just the name of the upper left cell of the Sheet, its there in case the UsedRange doesn't start at A1.

Call printRoutine

was shorthand for the printing routine that you have written.

That code says "Remember what the sheet looks like, do "printRoutine", put the sheet back to the remembered state"

You already have printRoutine.

[Bob Phillips]

Where is the code that inserts the details on multipole lines, it all looks like one line to me?

[Emoncada]

This is the code that makes it go to the next row.

[vba]For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(TxtOrdNum.Value)
.Cells(RowNext + i, 2) = TxtShipDate.Text
.Cells(RowNext + i, 3) = LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(Me.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = Me.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = Me.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = Me.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = CmbBoxProject.Value
.Cells(RowNext + i, 9) = LblRacf.Caption
.Cells(RowNext + i, 10) = CmbBoxClientName.Value
If Me.ChkBoxNewHire = True Then .Cells(RowNext + i, 11) = "YES"
End With
Next
FrmPrint.Show
End Sub [/vba]

Mike i don't see how or where it calls the userform back.

[Bob Phillips]

Yeah all in one row as I can see. So are you asking for a technique to put that datra on two rows?

[Emoncada]

This is the spreadsheet with a txt file. The txt file needs to be placed in "C:\MyData.txt" to be able to run the form.

Enter a couple of items and then click Save & Print button on bottom right then notice how data falls under several rows.

Hope this helps

[Bob Phillips]

No PrintRoutine, so a runtime error.

[Norie]

Will the order numbers be unique?

If so why not change the order number textbox to a combobox that lists all the order numbers?

Then the user can select the order they are interested in.

[gwkenny]

Hi, the following code gets you very close, but not all the way home. This can also be found in the attached file (based off of your file).

*** Begin Code Here ***
Sub GButton_Click()
Dim rngSel As Range
Dim rngLoop As Range
Dim intCounter As Integer

Set rngSel = Selection

'error trapping to determine if a valid row was selected
If rngSel.Worksheet.Name <> "Packing Slip Pim" Then
MsgBox "Please select an Order # cell on the 'Packing Slip Pim' worksheet"
Exit Sub
ElseIf rngSel.Cells.Count <> 1 Then
MsgBox "Only select one cell in Column A."
Exit Sub
ElseIf rngSel.Column <> 1 Then
MsgBox "The selection is not in Column A. Please select a cell in Column A and try again."
Exit Sub
ElseIf rngSel = "" Then
MsgBox "The cell you selected does not have an order number, please choose a cell with an order number"
Exit Sub
ElseIf rngSel.Row <= 2 Then
MsgBox "The cell you selected is in the title, not in the data. Please select an Order Number in the data and try again"
Exit Sub
End If

Load UserForm1
Set rngLoop = Range("A3")
intCounter = 1
While rngLoop <> ""
If rngLoop = rngSel Then
With UserForm1
.TxtOrdNum.Value = rngLoop
.TxtShipDate.Text = rngLoop.Offset(0, 1)
.LblShipVia.Caption = rngLoop.Offset(0, 2)
.Controls("TxtTrack" & intCounter).Value = rngLoop.Offset(0, 3)
.Controls("TxtSN" & intCounter).Value = rngLoop.Offset(0, 4)
.Controls("CmbBoxDesc" & intCounter).Value = rngLoop.Offset(0, 5)
.Controls("TxtQua" & intCounter).Value = rngLoop.Offset(0, 6)
.CmbBoxProject.Value = rngLoop.Offset(0, 7)
.LblRacf.Caption = rngLoop.Offset(0, 8)
.CmbBoxClientName.Value = rngLoop.Offset(0, 9)
If rngLoop.Offset(0, 10) = "YES" Then
.ChkBoxNewHire = True
Else
.ChkBoxNewHire = False
End If
End With

intCounter = intCounter + 1
End If
Set rngLoop = rngLoop.Offset(1, 0)
Wend
UserForm1.Show
End Sub
*** End Code Here ***

This code will repopulate your form though some things are empty like "Client Location". Don't know where you would get that from.

The BIG problem here is that if you hit "Print & Save" again, the data will be RE-ENTERED a second time on your sheet.

Now I can get around this. I can set a flag that if this is a re-opened form, and not a new form, then do NOT re-enter the information on the worksheet. That's a bandaid soluton.

However, I recommend that the code be reworked in the following manner.

Clicking the "Packing Slip" button starts a subroutine in Module 1.

This subroutine loads Userform1. Initializes Userform1, then passes control to Userform1 by "Userform1.show".

The code behind Userform1 should only be concerned with data validation and what happens when the Userform1 is shown. Then when you hit Print & Save, a flag is set on the Userform, and the Userform hides.

Control passes back to the module and the module code executes the print & save if a flag is set on the Userform (by the way of a flag, maybe the TAG of a control that indicates the Print & Save button was pressed to hide the form instead of the "X" in the upper right hand of the window).

This is a better way to code, because then you can use the same form in multiple ways (like what you are trying to do, use the form to enter data, and then also to review data).

Anyways, it's late and I'm tired. If what I did is getting you close to what you want, I can program in the bandaid solution later. That is if someone doesn't beat me to it.

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

[Emoncada]

Norie that was one of my thoughts also but how can i do that without duplicating the list with the order numbers?

Like an order can have 4 rows then Column A would have the order number 4 times so in the combobox it would show it 4 times instead of just once.

[Emoncada]

G That looks cool. Now i have code in the save button that checks Column A to see if the order already exists. would help fix that bandaid of duplicating the order?

[Emoncada]

ok i used G's code and it looks good only thing i noticed is i have this code
[vba]Private Sub LoadTextbox(ByRef cbo As MSForms.ComboBox, _
ByRef tx As MSForms.TextBox)
Dim ws As Worksheet, rngFind As Range
Set ws = ThisWorkbook.Sheets("List")
Set rngFind = ws.Range("A:A").Find(what:=cbo.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
tx.Value = rngFind.Offset(0, 1).Value
Else
tx.Value = "NOT FOUND!"
End If
End Sub[/vba]

and what that does is I have another worksheet hidden called "List" that has a column that CmbBoxDesc gets it's list from. So depending on what's selected it returns "EXPENSE", or "Phones" or "" <-- "" means it needs a serial number. So when this code from G pulls that data back instead of looking at the spreadsheet and pulling the Serial number that was entered it's pulling what ever the "List" worksheet says because of this code. Is there any way around that?

[Norie]

emoncada

There are various ways to populate a combobox with only unique values.

In fact I recently posted a link for another OP regarding that subject.

I'll see if I can find the thread and/or the link but you should have no problem finding either if you search the board.

Perhaps easiest to look for the recent thread, it was only in the last few days I posted there.

[Emoncada]

I came up with this
[VBA]Private Sub LoadTextbox(ByRef cbo As MSForms.ComboBox, _
ByRef tx As MSForms.TextBox)
Dim ws As Worksheet, rngFind As Range

Set ws = ThisWorkbook.Sheets("List")
If tx = "" Then
Set rngFind = ws.Range("A:A").Find(what:=cbo.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
tx.Value = rngFind.Offset(0, 1).Value
Else
tx.Value = "NOT FOUND!"
End If

Else
tx = tx
End If
End Sub[/VBA]

Does anyone see how this can cause a problem?