How to Recall Stored Data back into a Userform based on a Reference Number

[friesej]

I am trying to create a way to recall the data that has been input into my excel by my userform. I have looked EVERYWHERE and nothing works i need help.

vLookup doesnt seem to work for me or im not using it right.

Essentially i have 2 Userforms
Userform1 = Input Form
Userform2 = Recall Form

I have upto 22 different input options which i want to recall exactly as they are entered into the excel document (these are text boxes, combo boxes and tick boxes)

I also want to be able to edit what i recall after recalling it

I really need ALOT of help with this as i havent been able to find anything that will help and its driving me up the wall. I am not a VBA expert nothing close but can someone help me plz

 

[sijpie]

Let's start slowly here.

You have a userform1 that you use for data input. What happens in the code for the OK button on this form? There it will say where the various fields are stored in the sheet.

So then you also know where you can find the data.

Please post the code for the OK button for userform1. (use the code tags as shown below in red to post the code, please)

 

[shumidrives]

ive got a similar issue im wanting to recall data ive inputed and a few other bits into my worksheet back into the userform by using my button called "calculate" i also want to recall it by date

Private Sub Calculatebtn_click()



End Sub




Private Sub cbobowtype_DropButt*******()
    'Populate bow type control.
    Me.cbobowtype.AddItem "Recurve"
    Me.cbobowtype.AddItem "Bare Bow"
    Me.cbobowtype.AddItem "Long Bow"
    Me.cbobowtype.AddItem "Compound"
    Me.cbobowtype.AddItem "Other"
    
End Sub






Private Sub cbodistance_DropButt*******()
    'Populate distance control.
    Me.cbodistance.AddItem "20 yrds"
    Me.cbodistance.AddItem "30 yrds"
    Me.cbodistance.AddItem "50 yrds"
    Me.cbodistance.AddItem "70 yrds"
End Sub




Private Sub cboname_DropButt*******()
    'Populate name control.
    Me.cboname.AddItem "Andrew"
    Me.cboname.AddItem "Kayleigh"


End Sub






Private Sub worked_Click()
'worked or not
If worked.Value = True Then Me.worked.Value = 1
If worked.Value = False Then Me.worked.Value = 0


End Sub


Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Scoring sheet")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    
        .Cells(lRow, 1).Value = Me.txtdate.Value
        .Cells(lRow, 2).Value = Me.cbobowtype.Value
        .Cells(lRow, 3).Value = Me.cbodistance.Value
        .Cells(lRow, 4).Value = Me.worked.Value
        .Cells(lRow, 5).Value = Me.cboname.Value
        .Cells(lRow, 13).Value = Me.txtarrow1.Value
        .Cells(lRow, 14).Value = Me.txtarrow2.Value
        .Cells(lRow, 15).Value = Me.txtarrow3.Value
        .Cells(lRow, 16).Value = Me.txtarrow4.Value
        .Cells(lRow, 17).Value = Me.txtarrow5.Value
        .Cells(lRow, 18).Value = Me.txtarrow6.Value
        .Cells(lRow, 19).Value = Me.txtarrow7.Value
        .Cells(lRow, 20).Value = Me.txtarrow8.Value
        .Cells(lRow, 21).Value = Me.txtarrow9.Value
        .Cells(lRow, 22).Value = Me.txtarrow10.Value
        .Cells(lRow, 23).Value = Me.txtarrow11.Value
        .Cells(lRow, 24).Value = Me.txtarrow12.Value
        .Cells(lRow, 25).Value = Me.txtarrow13.Value
        .Cells(lRow, 26).Value = Me.txtarrow14.Value
        .Cells(lRow, 27).Value = Me.txtarrow15.Value
        .Cells(lRow, 28).Value = Me.txtarrow16.Value
        .Cells(lRow, 29).Value = Me.txtarrow17.Value
        .Cells(lRow, 30).Value = Me.txtarrow18.Value
        .Cells(lRow, 31).Value = Me.txtarrow19.Value
        .Cells(lRow, 32).Value = Me.txtarrow20.Value
        .Cells(lRow, 33).Value = Me.txtarrow21.Value
        .Cells(lRow, 34).Value = Me.txtarrow22.Value
        .Cells(lRow, 35).Value = Me.txtarrow23.Value
        .Cells(lRow, 36).Value = Me.txtarrow24.Value
        .Cells(lRow, 37).Value = Me.txtarrow25.Value
        .Cells(lRow, 38).Value = Me.txtarrow26.Value
        .Cells(lRow, 39).Value = Me.txtarrow27.Value
        .Cells(lRow, 40).Value = Me.txtarrow28.Value
        .Cells(lRow, 41).Value = Me.txtarrow29.Value
        .Cells(lRow, 42).Value = Me.txtarrow30.Value
        .Cells(lRow, 43).Value = Me.txtarrow31.Value
        .Cells(lRow, 44).Value = Me.txtarrow32.Value
        .Cells(lRow, 45).Value = Me.txtarrow33.Value
        .Cells(lRow, 46).Value = Me.txtarrow34.Value
        .Cells(lRow, 47).Value = Me.txtarrow35.Value
        .Cells(lRow, 48).Value = Me.txtarrow36.Value
        .Cells(lRow, 49).Value = Me.txtarrow37.Value
        .Cells(lRow, 50).Value = Me.txtarrow38.Value
        .Cells(lRow, 51).Value = Me.txtarrow39.Value
        .Cells(lRow, 52).Value = Me.txtarrow40.Value
        .Cells(lRow, 53).Value = Me.txtarrow41.Value
        .Cells(lRow, 54).Value = Me.txtarrow42.Value
        .Cells(lRow, 55).Value = Me.txtarrow43.Value
        .Cells(lRow, 56).Value = Me.txtarrow44.Value
        .Cells(lRow, 57).Value = Me.txtarrow45.Value
        .Cells(lRow, 58).Value = Me.txtarrow46.Value
        .Cells(lRow, 59).Value = Me.txtarrow47.Value
        .Cells(lRow, 60).Value = Me.txtarrow48.Value
        .Cells(lRow, 61).Value = Me.txtarrow49.Value
        .Cells(lRow, 62).Value = Me.txtarrow50.Value
        .Cells(lRow, 63).Value = Me.txtarrow51.Value
        .Cells(lRow, 64).Value = Me.txtarrow52.Value
        .Cells(lRow, 65).Value = Me.txtarrow53.Value
        .Cells(lRow, 66).Value = Me.txtarrow54.Value
        .Cells(lRow, 67).Value = Me.txtarrow55.Value
        .Cells(lRow, 68).Value = Me.txtarrow56.Value
        .Cells(lRow, 69).Value = Me.txtarrow57.Value
        .Cells(lRow, 70).Value = Me.txtarrow58.Value
        .Cells(lRow, 71).Value = Me.txtarrow59.Value
        .Cells(lRow, 72).Value = Me.txtarrow60.Value
    
    End With
    
    'Clear input controls.
    Me.cbobowtype.Value = ""
    Me.txtdate.Value = ""
    Me.cbodistance.Value = ""
    Me.worked.Value = ""
    Me.cboname.Value = ""
    Me.txtarrow1.Value = ""
    Me.txtarrow2.Value = ""
    Me.txtarrow3.Value = ""
    Me.txtarrow4.Value = ""
    Me.txtarrow5.Value = ""
    Me.txtarrow6.Value = ""
    Me.txtarrow7.Value = ""
    Me.txtarrow8.Value = ""
    Me.txtarrow9.Value = ""
    Me.txtarrow10.Value = ""
    Me.txtarrow11.Value = ""
    Me.txtarrow12.Value = ""
    Me.txtarrow13.Value = ""
    Me.txtarrow14.Value = ""
    Me.txtarrow15.Value = ""
    Me.txtarrow16.Value = ""
    Me.txtarrow17.Value = ""
    Me.txtarrow18.Value = ""
    Me.txtarrow19.Value = ""
    Me.txtarrow20.Value = ""
    Me.txtarrow21.Value = ""
    Me.txtarrow22.Value = ""
    Me.txtarrow23.Value = ""
    Me.txtarrow24.Value = ""
    Me.txtarrow25.Value = ""
    Me.txtarrow26.Value = ""
    Me.txtarrow27.Value = ""
    Me.txtarrow28.Value = ""
    Me.txtarrow29.Value = ""
    Me.txtarrow30.Value = ""
    Me.txtarrow31.Value = ""
    Me.txtarrow32.Value = ""
    Me.txtarrow33.Value = ""
    Me.txtarrow34.Value = ""
    Me.txtarrow35.Value = ""
    Me.txtarrow36.Value = ""
    Me.txtarrow37.Value = ""
    Me.txtarrow38.Value = ""
    Me.txtarrow39.Value = ""
    Me.txtarrow40.Value = ""
    Me.txtarrow41.Value = ""
    Me.txtarrow42.Value = ""
    Me.txtarrow43.Value = ""
    Me.txtarrow44.Value = ""
    Me.txtarrow45.Value = ""
    Me.txtarrow46.Value = ""
    Me.txtarrow47.Value = ""
    Me.txtarrow48.Value = ""
    Me.txtarrow49.Value = ""
    Me.txtarrow50.Value = ""
    Me.txtarrow51.Value = ""
    Me.txtarrow52.Value = ""
    Me.txtarrow53.Value = ""
    Me.txtarrow54.Value = ""
    Me.txtarrow55.Value = ""
    Me.txtarrow56.Value = ""
    Me.txtarrow57.Value = ""
    Me.txtarrow58.Value = ""
    Me.txtarrow59.Value = ""
    Me.txtarrow60.Value = ""
    
    
       
        
End Sub


Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
    
End Sub

 

[sijpie]

Sorry for the late reply, was away.

First of all, in your cmdAdd_Click() you address each of the 60(!) text fields individually. That is a lot of coding. What I have done in the version below is have VBA loop through each control and if it has the correct name, then extract the number and act on that. You can see how much more compact and flexible the code becomes. Now you can add or remove any number of text boxes and it will still work without recoding.

THen, it is very easy to read the data from your sheet into the form (just the opposite of writing) but I assume you have a problem deciding how to select the correct row? How do you want your user to select which data needs to be filled into the form? Does (s)he first fill out cboName, txtDate, cboBowtype, cboDistance, and cboWorked, or just a few of these?

Let me know.


Improved code:

Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim lRow As Long, lN As Long
    Dim ws As Worksheet
    Dim ctrlTF As MSForms.Control
    
    Set ws = Worksheets("Scoring sheet")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    
        .Cells(lRow, 1).Value = Me.txtdate.Value
        .Cells(lRow, 2).Value = Me.cbobowtype.Value
        .Cells(lRow, 3).Value = Me.cbodistance.Value
        .Cells(lRow, 4).Value = Me.worked.Value
        .Cells(lRow, 5).Value = Me.cboname.Value
        
        For Each ctrlTF In Me.Controls
            ' go through each control in the form, if control has _
              name txtarrowXX then copy value and clear
            If Left(ctrlTF.Name, 8) = "txtarrow" Then
                ' copy control value to sheet cell
                lN = VBA.Val(Right(ctrlTF.Name, Len(ctrlTF.Name) - 8))
                .Cells(lRow, lN + 12).Value = ctrlTF.Value
                
                ' clear control
                ctrlTF.Value = ""
            End If
        Next ctrlTF
    End With
    
    'Clear remaining input controls.
    Me.cbobowtype.Value = ""
    Me.txtdate.Value = ""
    Me.cbodistance.Value = ""
    Me.worked.Value = ""
    Me.cboname.Value = ""
    
        
End Sub