Populate selected row from form

JenniferN · Jan 8, 2008

Hi,

Thought it was better to start a new thread than to continue the old one since the focus in that thread was to get the values into a form and the question this tme is to get the values back into the correct row.

Background. My little application does the following...
In Form1 the user adds information that is inserted as a new row in my excel sheet. A unique ID is created and inserted in column A.
In Form2 the user sees a listbox with the rows in the excel sheet. The user then selects one of the rows and get all the information including the ID displayed to him/her in Form3.
In Form3 the user should then be able to update the values (except the ID) and then the values should be inserted back into the excel sheet in the correct row. I suppose I need the code behind my OKbutton to say something like

If Column A = value of IDBox, insert values in columnB, columnC etc...
But how do I write this code correctly?

BrianB · Jan 8, 2008

I think you are over-complicating matters.

If you look at my code closely you will see that it *sets the data row first* before the form opens (and subsequently by using the Next and Previous record buttons) , and automatically pulls data into the form from that row when it is opened. When the form is closed or moved to another record it automatically puts *all* its data into the database - even though some or all of it has not been changed. This means that there are no decisions to be made and the code is very simple.

When a new record is required all you need to do is set the form to the next empty row. The form reads the empty cells, so clearing its text boxes etc., and writes any data back to that row when the user moves to another record or closes the form.

Your real task is to add some sort of data validation to the "Private Sub UpdateForm ()" routine. Again, it will be validating data already validated when no changes have been made, but this does not matter. In the "olden days" we had worries about how long things like this would take. Modern computers get it all done in a microsecond.

Have a look at my code again. You really do not have to add much to it to get everything to work.

JenniferN · Jan 8, 2008

Hi,

And thanks for the fast reply
I think I understand the basics of how your terrific example works, but of course I might be wrong. The idea with my duplicate form(3) is to be even more user friendly and prevent people from adding a new row when all they really want is to update an old one. I thought I'd give them no option but to either update a row or cancel the update. That's the reason for Form3 which is almost a copy (just the ID textbox is new) of Form1. The only real difference should be that the OK button in Form1 adds a new row and the OK button in Form3 is suppose to update the row selected. I've been playing around with your code and added a form2 with a listbox and a copy of form1 called form3. But so far all I've gotten my code to do is to display the values from the selected row in form3, but thereafter the values are inserted into row1 (due to initialize event I presume) and with the next use of the New button the values are inserted into a new row in the bottom of the table.

I thought that displaying the values in Form3 after displaying them in a listbox in Form2 might loose "the connection" to the row selected in the worksheet since I in the listbox code tell the values what textbox they should be inserted into in Form3. That's why I thought I might have to go by the ID to insert them back into the same row. Is this an incorrect assumption?

Listbox code

Code:

Private Sub OKButton2_Click()
    If ListBox1.ListIndex <> -1 Then
       UserForm3.TextBox3.Value = ListBox1.Column(0)
       UserForm3.DateBox3.Value = ListBox1.Column(1)
       UserForm3.StatusBox3.Value = ListBox1.Column(2)
       UserForm3.ClosingBox3.Value = ListBox1.Column(3)
       UserForm3.HeadingBox3.Value = ListBox1.Column(4)
       UserForm3.SummaryBox3.Value = ListBox1.Column(5)
       UserForm3.CommentsBox3.Value = ListBox1.Column(6)
       UserForm3.TestspecBox3.Value = ListBox1.Column(7)
       UserForm3.SeverityBox3.Value = ListBox1.Column(8)
       UserForm3.EnvBox3.Value = ListBox1.Column(9)
       UserForm3.VersionBox3.Value = ListBox1.Column(10)
       UserForm3.SubsysBox3.Value = ListBox1.Column(11)
       UserForm3.FormBox3.Value = ListBox1.Column(12)
       UserForm3.TesterBox3.Value = ListBox1.Column(13)
       UserForm3.ResponsibleBox3.Value = ListBox1.Column(14)
       UserForm3.FixedVerBox3.Value = ListBox1.Column(15)
    End If
    UserForm3.Show
End Sub

JenniferN · Jan 12, 2008

I still haven't been able to get this one working the way I want to and I really do want it to work.

Does anyone have any good ideas that I can try? I've done a little change and that is added an id text box to my userform1 so my values are always added or changed in the same userform. This however doesn't solve my problem, which is that I just don't seem to be able to get the values back into the row that they were extracted from.

The best way (and I'm completely guessing here) might be this...
I have an id created when a new record is inserted which is later displayed with all other values when I do a search. Thereafter I do necessary changes to my data. When I then click OK the following should happen... If the id textbox is empty then take the last row +1 which would trigger the add id code. If the id textbox have a value check the value and add all values to that row again. But how do I do this?

I suppose somewhere in the lines below I should have some code checking the id box and then getting the values back into the correct row if the id box has a value. Please help.

Code:

Private Sub OKButton_Click()
    ThisWorkbook.Save
    Dim NewRow As String
    Dim NewNumber As String
    '---------------------------------------------------
    Sheets("Systemtest").Activate
    With Worksheets("Systemtest")
        '-----------------------------------------------
        '- next row
        NewRow = .Range("A65536").End(xlUp).Row + 1
        '-----------------------------------------------
        '- add 1 to previous count
        NewNumber = .Cells(NewRow - 1, 1).Value + 1
        '-----------------------------------------------
        '- put values to worksheet
        .Cells(NewRow, 1).Value = NewNumber
        .Cells(NewRow, 2).Value = DateBox
        .Cells(NewRow, 3).Value = StatusBox
        .Cells(NewRow, 4).Value = ClosingBox
        .Cells(NewRow, 5).Value = HeadingBox
        .Cells(NewRow, 6).Value = SummaryBox
        .Cells(NewRow, 7).Value = CommentsBox
        .Cells(NewRow, 8).Value = TestspecBox
        .Cells(NewRow, 9).Value = SeverityBox
        .Cells(NewRow, 10).Value = EnvBox
        .Cells(NewRow, 11).Value = VersionBox
        .Cells(NewRow, 12).Value = SubsysBox
        .Cells(NewRow, 13).Value = FormBox
        .Cells(NewRow, 14).Value = TesterBox
        .Cells(NewRow, 15).Value = ResponsibleBox
        .Cells(NewRow, 16).Value = FixedVerBox
        '-----------------------------------------------
    End With
    ThisWorkbook.Save
    MsgBox "Unique id: " & NewNumber
    
    'Clear the controls for the next entry
    DateBox.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

JenniferN · Jan 14, 2008

I've done this little attempt to try and solve my problem, but with no luck. I assume I have to somehow set the currentrow in some other way. But how do I do this?

Code:

Private Sub OKButton_Click()
    Dim NewRow As String
    Dim NewNumber As String
    Dim CurrentRow As String
        '---------------------------------------------------
    Sheets("Systemtest").Activate
    With Worksheets("Systemtest")
       If IDBox = "" Then
        '-----------------------------------------------
        '- next row
        NewRow = .Range("A65536").End(xlUp).Row + 1
        '-----------------------------------------------
        '- add 1 to previous count
        NewNumber = .Cells(NewRow - 1, 1).Value + 1
        '-----------------------------------------------
        '- put values to worksheet
        .Cells(NewRow, 1).Value = NewNumber
        .Cells(NewRow, 2).Value = DateBox
        .Cells(NewRow, 3).Value = StatusBox
        .Cells(NewRow, 4).Value = ClosingBox
        .Cells(NewRow, 5).Value = HeadingBox
        .Cells(NewRow, 6).Value = SummaryBox
        .Cells(NewRow, 7).Value = CommentsBox
        .Cells(NewRow, 8).Value = TestspecBox
        .Cells(NewRow, 9).Value = SeverityBox
        .Cells(NewRow, 10).Value = EnvBox
        .Cells(NewRow, 11).Value = VersionBox
        .Cells(NewRow, 12).Value = SubsysBox
        .Cells(NewRow, 13).Value = FormBox
        .Cells(NewRow, 14).Value = TesterBox
        .Cells(NewRow, 15).Value = ResponsibleBox
        .Cells(NewRow, 16).Value = FixedVerBox
        '-----------------------------------------------
    
    MsgBox "Unique id: " & NewNumber
    
    Else
    CurrentRow = IDBox
     '-----------------------------------------------
        '- current row
        'CurrentRow = .Range("A65536")
        '-----------------------------------------------

        
    '- update values to worksheet
        
        .Cells(CurrentRow, 2).Value = DateBox
        .Cells(CurrentRow, 3).Value = StatusBox
        .Cells(CurrentRow, 4).Value = ClosingBox
        .Cells(CurrentRow, 5).Value = HeadingBox
        .Cells(CurrentRow, 6).Value = SummaryBox
        .Cells(CurrentRow, 7).Value = CommentsBox
        .Cells(CurrentRow, 8).Value = TestspecBox
        .Cells(CurrentRow, 9).Value = SeverityBox
        .Cells(CurrentRow, 10).Value = EnvBox
        .Cells(CurrentRow, 11).Value = VersionBox
        .Cells(CurrentRow, 12).Value = SubsysBox
        .Cells(CurrentRow, 13).Value = FormBox
        .Cells(CurrentRow, 14).Value = TesterBox
        .Cells(CurrentRow, 15).Value = ResponsibleBox
        .Cells(CurrentRow, 16).Value = FixedVerBox
        
        MsgBox "The following Id has been updated: " & CurrentRow
    End If
    
    'Clear the controls for the next entry
    IDBox.Text = ""
    DateBox.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 With
End Sub

BrianB · Jan 16, 2008

My code is complete and working as is. I suggest you go back to that and adapt it to do what you want.

JenniferN · Jan 16, 2008

Hi BrianB,

No arguments from me. Your code works perfectly and your probably right that I'm overcomplicating things.

However, I'm a newbie at this and my problem remains

I would love it if you had the time to take a look at my application, of course, I do understand if that isn't possible for you.

JenniferN · Jan 17, 2008

Hi again,

Since I've just now seen how my application works when it's used by many simultaniously (share workbook) I think I can drop this question. I'm going to give it a try in Access (which I've never used so that will certainly be very interesting).

I'll probably be back shortly with new threads regarding Access.

But for now, thanks for all the help.

AlpineCI · Mar 12, 2008

JenniferN said:
Hi,

Thought it was better to start a new thread than to continue the old one since the focus in that thread was to get the values into a form and the question this tme is to get the values back into the correct row.

Background. My little application does the following...
In Form1 the user adds information that is inserted as a new row in my excel sheet. A unique ID is created and inserted in column A.
In Form2 the user sees a listbox with the rows in the excel sheet. The user then selects one of the rows and get all the information including the ID displayed to him/her in Form3.
In Form3 the user should then be able to update the values (except the ID) and then the values should be inserted back into the excel sheet in the correct row. I suppose I need the code behind my OKbutton to say something like

If Column A = value of IDBox, insert values in columnB, columnC etc...
But how do I write this code correctly?

Jennifer, this is pretty close to what I want to do with my VBA program. I want to have a userform to enter data into which will insert it into the proper column in a worksheet. Then, I want to be able to search in that worksheet by date. However, there may be multiple rows with the same date. So....I want to be able to view these multiple instances and select the correct one. Another way to solve this would be to search by date, then search by another field to narrow it down even more. Somehow, after the first search pass, it will have to search only the fields with that given date. That is where I get stuck. Could you please send me your code and maybe we could help each other out. Thanks.

-Dave

JenniferN · Mar 13, 2008

Hi Dave,

If you're interested I have two files that might help. None of them works perfectly today, but I think that the "answer" might not be so far away.

I think I might be able to get it working myself but I just don't have the time right now. Is it possible for me to somehow e-mail the complete files to you?

/Jennifer

Populate selected row from form

JenniferN

New Member

Excel Facts

BrianB

Well-known Member

JenniferN

New Member

JenniferN

New Member

JenniferN

New Member

BrianB

Well-known Member

JenniferN

New Member

JenniferN

New Member

AlpineCI

New Member

JenniferN

New Member

Similar threads

Forum statistics

Share this page

Populate selected row from form

New Member

Excel Facts

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock