Populate form from row in excel sheet

[JenniferN]

Hi,

I'm back with my little bug reporting tool and now I would like to know how I populate a form with values from a row in my excel sheet that i selected in a multicolumn textbox.

My "tool" works like this... The user opens the excel file and can choose one of two buttons, Add defect and Find defect. When the Add button is clicked Form1 is opened and the user fills in a number of fields which are then inserted into an excel sheet (same book though). Then there's the Find button. When the user clicks this button Form2 opens with a multicolumn textbox that displays some of the columns with some of the previously inserted information. Now I would like to be able to select one row and get Form1 populated with the values for that particular row. The user should then be able to change some of the values and the changes should be inserted back into the correct row in my excel sheet. How in the world do I do this??? Right now I just open my Form1 when I select a row and click an OK button. How do I get the values from my excel sheet back into my fields?I've tried to copy code from an example I found, but I can't get it to work.

My first form where I add my data

Private Sub UserForm1_Initialize()
Dim rIds As Range
Dim MaxId As Long

Set rIds = Worksheets("Systemtest").Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
MaxId = Application.WorksheetFunction.Max(rIds)
With Me
.IdBox.Value = MaxId


Private Sub DateBox_Change()
   DateBox = Format(Date, "yy/mm/dd")
End Sub

Private Sub HeadingBox_Change()

End Sub

Private Sub IdBox_Change()
'   =IF(COUNTA(Systemtest[1]:Systemtest[1])=1,COUNTA(Systemtest[1]:Systemtest[1]),"""")

End Sub



Private Sub StatusBox_Change()
    StatusBox.List = Array("New", "Open", "In Progress", "Fixed", "Closed", "Reopen", "Rejected", "Pending")
'    StatusBox.Value = "New"
End Sub

Private Sub SeverityBox_Change()
    SeverityBox.List = Array("Critical", "Major", "Normal", "Minor", "Cosmetic", "Improvement")
'    SeverityBox.Value = "Normal"
End Sub

Private Sub EnvBox_Change()
    EnvBox.List = Array("NLL", "JLL", "NLL/JLL", "Halland")
'    SeverityBox.Value = "NLL"
End Sub

Private Sub SummaryBox_Change()

End Sub

Private Sub VersionBox_Change()
    VersionBox.List = Array("3.5.aa.1", "3.5.aa.2", "3.5.aa.3", "3.5.aa.4", "3.5.ab.1")
'    SeverityBox.Value = "3.5.aa.1"
End Sub

Private Sub SubsysBox_Change()
    SubsysBox.List = Array("Ankomstreg (Ö,S)", "Diagnosreg", "Generella", _
    "IVA", "Infektionsreg", "Integration", "Journal", "LAB", "Läkemedel", "Läkarintyg/utl", _
    "Operation", "PAS Generella", "Pako", "Paramedicin", "Patient", "Remisser", "Röntgen", "System", _
    "Tandvårdsadm", "Vårddok", "Vårdkontakt")
'    SubsysBox.Value = "Generella"
End Sub

Private Sub FormBox_Change()

End Sub

Private Sub TesterBox_Change()
    TesterBox.List = Array("ast", "bng", "dll", "esi", "ewalun", "frea", "jfn", "kata", "larb", "lln", "mem", "mhd", "mlm", "moae", "mwn", "ulwi")
'    TesterBox.Value = "ast"
End Sub

Private Sub ResponsibleBox_Change()
    ResponsibleBox.List = Array("ast", "dll", "esi", "frea", "hkn", "jfn", "kata", "larb", "lln", "mem", "mhd", "mlm", "moae", "mwn", "ulwi")
'    ResponsibleBox.Value = "ast"
End Sub

Private Sub FixedVerBox_Change()
    FixedVerBox.List = Array("3.5.aa.2", "3.5.aa.3", "3.5.aa.4", "3.5.ab.1", "3.5.ab.2")
'    FixedVerBox.Value = "3.5.aa.2"
End Sub
Private Sub CommentsBox_Change()

End Sub

Private Sub ClosingBox_Change()
    ClosingBox = Format(Date, "yy/mm/dd")
End Sub


Private Sub OKButton_Click()
'   Make sure Systemtest is active
    Sheets("Systemtest").Activate
    
'   Determine the next empty row
    NextRow = _
        Application.WorksheetFunction.CountA(Range("A:A")) + 1
'   Transfer the information
    Cells(NextRow, 1) = IdBox
    Cells(NextRow, 2) = DateBox
    Cells(NextRow, 3) = StatusBox
    Cells(NextRow, 4) = ClosingBox
    Cells(NextRow, 5) = HeadingBox
    Cells(NextRow, 6) = SummaryBox
    Cells(NextRow, 7) = CommentsBox
    Cells(NextRow, 8) = TestspecBox
    Cells(NextRow, 9) = SeverityBox
    Cells(NextRow, 10) = EnvBox
    Cells(NextRow, 11) = VersionBox
    Cells(NextRow, 12) = SubsysBox
    Cells(NextRow, 13) = FormBox
    Cells(NextRow, 14) = TesterBox
    Cells(NextRow, 15) = ResponsibleBox
    Cells(NextRow, 16) = FixedVerBox
    
    
    
    
    
'   Clear the controls for the next entry
    IdBox.Text = ""
    StatusBox.Text = ""
    HeadingBox.Text = ""
    SummaryBox.Text = ""
    CommentsBox.Text = ""
    SeverityBox.Text = ""
    EnvBox.Text = ""
    VersionBox.Text = ""
    SubsysBox.Text = ""
    FormBox.Text = ""
    TestspecBox.Text = ""
'   TesterBox.Text = ""
    ResponsibleBox.Text = ""
    FixedVerBox.Text = ""
    ClosingBox.Text = ""
        
    OptionUnknown = True
    StatusBox.SetFocus
End Sub
Private Sub RegNewButton_Click()

End Sub
Private Sub CancelButton_Click()
    Unload UserForm1
End Sub

My second form where I have my multicolumn textbox and where I'm suppose to select a row and get Form1 displayed with all data for a particular row

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
            ListBox1.List(r, 3) = .Offset(0, 4).Value
            ListBox1.List(r, 4) = .Offset(0, 5).Value
            ListBox1.List(r, 5) = .Offset(0, 7).Value
            ListBox1.List(r, 6) = .Offset(0, 12).Value
            ListBox1.List(r, 7) = .Offset(0, 14).Value
            ListBox1.List(r, 8) = .Offset(0, 13).Value
            ListBox1.List(r, 9) = .Offset(0, 15).Value
          End With
        r = r + 1
    Next Cell
End Sub

Private Sub Frame1_Click()

End Sub

Private Sub ListBox1_Click()

End Sub


Private Sub OKButton2_Click()
    UserForm1.Show
    Dim FirstId As String
    Dim strFind As String   'what to find
    Dim rSearch As Range    'what range to search
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL, fndM, fndN, fndO, fndP As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12, head13, head14, head15, head16 'headings for list
    Dim i As Integer
    i = 1
    Set rSearch = Systemtest.Range("a2", Range("a65536").End(x1Up))
    Str.Find = Me.TextBox1.Value
    With rSearch = Systemtest.Range
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            'load the headings
            head1 = Range("a2").Value
            head2 = Range("b2").Value
            head3 = Range("c2").Value
            head4 = Range("d2").Value
            head5 = Range("e2").Value
            head6 = Range("f2").Value
            head7 = Range("g2").Value
            head8 = Range("h2").Value
            head9 = Range("i2").Value
            head10 = Range("j2").Value
            head11 = Range("k2").Value
            head12 = Range("l2").Value
            head13 = Range("m2").Value
            head14 = Range("n2").Value
            head15 = Range("o2").Value
            head16 = Range("p2").Value
            
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
                MyArray(0, 7) = head8
                MyArray(0, 8) = head9
                MyArray(0, 9) = head10
                MyArray(0, 10) = head11
                MyArray(0, 11) = head12
                MyArray(0, 12) = head13
                MyArray(0, 13) = head14
                MyArray(0, 14) = head15
                MyArray(0, 15) = head16
             End With
             FirstId = c.Id
             Do
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fndE = c.Offset(0, 4).Value
                fndF = c.Offset(0, 5).Value
                fndG = c.Offset(0, 6).Value
                fndH = c.Offset(0, 7).Value
                fndI = c.Offset(0, 8).Value
                fndJ = c.Offset(0, 9).Value
                fndK = c.Offset(0, 10).Value
                fndL = c.Offset(0, 11).Value
                fndM = c.Offset(0, 12).Value
                fndN = c.Offset(0, 13).Value
                fndO = c.Offset(0, 14).Value
                fndP = c.Offset(0, 15).Value
                
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                MyArray(i, 9) = fndJ
                MyArray(i, 10) = fndL
                MyArray(i, 11) = fndM
                MyArray(i, 12) = fndN
                MyArray(i, 13) = fndO
                MyArray(i, 14) = fndP
                i = i + 1
                Set c = .FindNext(c)
                End If
            End With
            'Load data into LISTBOX
            Me.ListBox1.List() = MyArray
End Sub
            
                

Private Sub CancelButton2_Click()
Unload UserForm2
End Sub

Private Sub UserForm_Click()

End Sub

As you probably can see from my code... I'm completely lost and I haven't got a clue what I'm doing.

 

[BrianB]

Hopefully my code here will help ..

http://www.mrexcel.com/board2/viewtopic.php?t=272831

 

[JenniferN]

Hi Brian,

Thanks for a great example. It actually does everything I want, but I still have one problem. Do you know what I have to do to be able to make my selection from a row in a listbox instead of an inputbox that you use? I get a listbox displayed when using my Findbutton and can select the row I want, but when I click my OKbutton nothing happens since I'm not quite sure what I have to write to display my form with the values.

Kind regards,
Jennifer

 

[BrianB]

Sorry, there is too much code to go through line by line.
Top marks for the formatting. It would not have got a look at all otherwise.

Put a breakpoint in your ok button code, click the button, then step through using F8 key.
If you can tell me exactly where the problem lies I might be able to help.

 

[John_w]

Not sure if I'm reading this question as simpler than it really is, but try something like this:

Private Sub CommandButton1_Click()
    If ListBox1.ListIndex <> -1 Then
        UserForm1.TextBox1.Value = ListBox1.Column(0)
        UserForm1.TextBox2.Value = ListBox1.Column(1)
        UserForm1.TextBox3.Value = ListBox1.Column(2)
        UserForm1.TextBox4.Value = ListBox1.Column(3)
    End If
End Sub

This populates the fields (here they happen to be text boxes, but the principal is the same whatever the object type) on Form1 with the values in the selected row from the listbox on Form2.

 

[JenniferN]

Hm, I'm not quite sure how to describe this properly. Here's my code for opening my form containing just a listbox and an OK and a Cancel button. The listbox has three columns displaying what was entered by the use of the code in your example

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    Set Sh = Worksheets("Blad1")
    With Sh
        Set Rng = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
          End With
        r = r + 1
    Next Cell
End Sub
Private Sub UpdateForm()
    TextBox1.Value = DataSheet.Cells(CurrentRow, 1).Value
    TextBox2.Value = DataSheet.Cells(CurrentRow, 2).Value
    TextBox3.Value = DataSheet.Cells(CurrentRow, 3).Value
End Sub

Private Sub OKButton_Click()

End Sub

Private Sub CancelButton_Click()
    Unload UserForm2
End Sub

I suppose I need code similar to yours in this part

Private Sub FindButton_Click()
    Dim FoundCell As Object
    Dim MyValue As String
    '------------------------------------------------------
    '- get value
    MyValue = InputBox("Value to find in column A", " FIND")
    If MyValue = "" Then Exit Sub
    '-------------------------------------------------------
    '- find value
    With DataSheet.Range("A:A")
        Set FoundCell = .Find(MyValue, LookIn:=xlValues, lookat:=xlPart)
    End With

to put in my sub for the OKbutton, but instead of MyValue = "InputBox"... I need to put the row I select in a string or something and then insert those values back into the textboxes in the first form. How do I do that? Am I confusing? I'm very confused myself.

 

[JenniferN]

To John_w: Thank you so much. It worked perfectly.

 

[JenniferN]

One (hopefully) last question... Do I have to add something when the data isn't on the same sheet as the Find button I use? The find button is in sheet1 and the columns that holds the data is in sheet2. I'm wondering since I got an error message saying something like "An object is required" (translation from my first language) when I copied the code and just changed the name of the Listbox.

Private Sub OKButton2_Click()
    If ListBox1.ListIndex <> -1 Then
       UserForm1.DateBox.Value = DateBox.Column(0)
    End If
    UserForm1.Show
End Sub

 

[BrianB]

Glad your first problem got solved. I did not understand the question because, looking at your code I got the impression that you understood that point.

Similar thing here. When using code of this complexity, especially when using more than one worksheet, we always need to exactly specify the full reference eg. Worksheets("Sheet1").Range(A1").Value. It then does not matter where anything is.

You are using the same variable name twice so you need to explicitly include the container ("parent") of the second Datebox. I cannot see how the second one relies on a worksheet because, in the code, it looks like repeat of the first one. The first half of this is correct, you need to get the second half correct.

UserForm1.DateBox.Value = DateBox.Column(0)

Also, you have this code :

Private Sub DateBox_Change()
DateBox = Format(Date, "yy/mm/dd")
End Sub

So, even if you do manage to change the DateBox.Value it will not make any difference - it will immediately change back to today's Date anyway.



Hope this helps.

 

[JenniferN]

You shouldn't be fooled by first impressions, cause I'm a complete rookie at this. Where do I specify the full reference that you mention and how do I include the "parent" of the datebox? I'm very new at this and I've noticed that what I'm trying to accomplish isn't as easy as I would have liked, but I seem to learn something new everyday at least

What do you mean by the comment about the Datebox syntax? The purpose with the datebox is that the first time the user adds information it should have the correct date and thereafter it really shouldn't be changed at all so I'm kind of hoping that people won't mess with that particular field when searching and displaying previously inserted information.