Updating record from Userform

daren.beaney · Aug 11th 2015

Hi, I'm pretty new to forms and I've been struggling with trying to get a user form to update the active record it's displaying. The form includes a listbox displaying the list of records within the sheet and the form fields display the data relating to the selected record in the listbox.

Unfortunately I can't workout how to use an update button to update the cells regardless whether changes were made or not to the content because it's a dynamic list in the form. I've attached my sample workbook.

Any help on this will be massively appreciated.

cytop · Aug 11th 2015

Re: UserForm - Updating active record from user form.

Seeing as you already have code to update a particular row...

Code

Private Sub cmdUpdate_Click()


   Dim r As Excel.Range
   
   Set r = Worksheets("ES").Range("A:A").Find(What:=lstMyData.List(lstMyData.ListIndex, 0), lookat:=xlWhole)
   
   If Not r Is Nothing Then


      With Worksheets("ES")
         .Range("B" & r.Row).Value = TextBox1.Value
         ...
         ...  
   End If


   Set r = Nothing
   
End Sub

Display More

daren.beaney · Aug 11th 2015

Re: UserForm - Updating active record from user form.

Thanks for this quick response, that worked a treat with a small tweak.

Code

Private Sub cmdUpdate_Click()
     
    Dim r As Excel.Range
     
    Set r = Worksheets("ES").Range("A:A").Find(What:=lstMyData.List(lstMyData.ListIndex, 0), lookat:=xlWhole)
     
    If Not r Is Nothing Then
         
        With Worksheets("ES")
            .Range("AD" & r.Row).Value = Control9.Value 'AD being the related column that needs to be updated with the value
            ...
            ...
        End With
      End If
    Set r = Nothing
         
    End Sub

Display More

daren.beaney · Aug 12th 2015

Re: UserForm - Updating active record from user form.

Hi sorry, I responded without testing it properly the code seems to jump once it completes the first .Range... line. any ideas how to update the cells as per the code in the attached.

cytop · Aug 12th 2015

Re: UserForm - Updating active record from user form.

Quote

...responded without testing it properly

Sorry, but there's nothing wrong with the code I posted.

If it 'jumps' then either an error has been raised or else you are using controls bound to a range - either would have been an existing issue with the code which I didn't check as it wasn't an issue flagged by you in your original post.

Unfortunately, am unable to check now as I'm using a phone.

daren.beaney · Aug 12th 2015

Re: UserForm - Updating active record from user form.

Well this is what I have however after it goes through the first .Range... it jumps to refreshing the form which to me seems to be related to the lstMyData (second piece of code).

Code

Private Sub cmdUpdate_Click()
     
    Dim r As Excel.Range
     
    Set r = Worksheets("ES").Range("A:A").Find(What:=lstMyData.List(lstMyData.ListIndex, 0), lookat:=xlWhole)
     
    If Not r Is Nothing Then
         
        With Worksheets("ES")
            .Range("AB" & r.Row).Value = Control8.Value 'Code jumps from here to refresh the page and again after each line below.
            .Range("AI" & r.Row).Value = Control9.Value
            .Range("AJ" & r.Row).Value = Control10.Value
            .Range("AK" & r.Row).Value = Control11.Value
            .Range("AL" & r.Row).Value = Control12.Value
            .Range("AM" & r.Row).Value = Control13.Value
            .Range("AO" & r.Row).Value = Control14.Value
            .Range("AP" & r.Row).Value = Control15.Value
        End With
      End If
    Set r = Nothing
         
    End Sub

Display More

The code it bounces to to reload the form:

Code

Sub lstMyData_Click()
'dim the variables
Dim i As Integer




'find the selected list item
i = Me.lstMyData.ListIndex
Me.lstMyData.Selected(i) = True




'add the values to the text boxes
Me.Control1.Value = Me.lstMyData.Column(0, i)
Me.Control2.Value = Me.lstMyData.Column(28, i)
Me.Control3.Value = Me.lstMyData.Column(31, i)
Me.Control4.Value = Me.lstMyData.Column(32, i)
Me.Control5.Value = Me.lstMyData.Column(33, i)
Me.Control6.Value = Me.lstMyData.Column(1, i)
Me.Control7.Value = Me.lstMyData.Column(2, i)
Me.Control8.Value = Me.lstMyData.Column(27, i)
Me.Control9.Value = Me.lstMyData.Column(29, i)
Me.Control10.Value = Me.lstMyData.Column(34, i)
Me.Control11.Value = Me.lstMyData.Column(35, i)
Me.Control12.Value = Me.lstMyData.Column(36, i)
Me.Control13.Value = Me.lstMyData.Column(37, i)
Me.Control14.Value = Me.lstMyData.Column(38, i)
Me.Control15.Value = Me.lstMyData.Column(39, i)




End Sub

Display More

The final result overwrites the changes made in the form with data in the worksheet.

cytop · Aug 12th 2015

Re: UserForm - Updating active record from user form.

You're beginning to see the payback for the convenience of Bound Controls. You update a cell which triggers a refresh of the listbox and your code cascades from there...

My opinion only, but using bound controls when the data is updated is more trouble than it is worth. I would seriously suggest you try another approach. It takes a little setting up, but once done, it makes things a lot easier.

If you're willing to try...

Remove the setting for RowSource for myLstData
Add a new worksheet and name it, for example, 'Map' (This can be hidden/very hidden when the workbook is released to users)
Add formulas to Row1 referring to the column headings on sheet ES (Or else just Copy/Paste - but formulas are easier if something changes later)
In Row 2 add the control names under the headings they relate to (Item - Control1, Service Offering - Control6 etc...)
Create a named range on Row 2, Name it for the user form (Currently UserForm1). It should extend as far as column headings exist.

Code changes

The UserForm_Initialize event is used to load data to the listbox. It also stores the row number in Column 1 (Hidden).

Code

Private Sub UserForm_Initialize()


   Dim r As Excel.Range
   
   With lstMyData
      
      '// change to suit
      .ColumnCount = 5
      '// Change widths as needed
      .ColumnWidths = "0;50;125;125;125"
   
      '// Decide on which columns to display. Nobody needs 40 odd columns in a Listbox.
      For Each r In Sheets("ES").Range(Sheets("ES").Range("A2"), Sheets("ES").Range("A" & Rows.Count).End(xlUp))
         .AddItem
         .List(.ListCount - 1, 0) = r.Row
         .List(.ListCount - 1, 1) = r.Value
         .List(.ListCount - 1, 2) = r.Offset(, 1).Value
         .List(.ListCount - 1, 3) = r.Offset(, 2).Value
         .List(.ListCount - 1, 4) = r.Offset(, 3).Value
         '...
         '...
      Next
      
   End With
   
End Sub

Display More

MyLstData click event.

This simply loops though all the controls on the userform, searches for the control name in the 'UserForm1' named range. If found, it uses the column number where the name was found and the row number stored on Column 1 of the list box to load data from that cell on ES.

Code

Private Sub lstMyData_Click()


   Dim ctl As Control
   Dim r As Excel.Range
   
   For Each ctl In Me.Controls
      
      Set r = Range(Me.Name).Find(What:=ctl.Name, Lookat:=xlWhole, MatchCase:=False)
      
      If Not r Is Nothing Then
         ctl.Text = Sheets("ES").Cells(Val(lstMyData.List(lstMyData.ListIndex, 0)), r.Column)
      End If
   Next


End Sub

Display More

And Finally, the Update code - exactly the same as the Click event, but it writes to the worksheet

Code

Private Sub cmdUpdate_Click()


   Dim r                As Excel.Range
   Dim ctl              As Control


   For Each ctl In Me.Controls
      Set r = Range(Me.Name).Find(What:=ctl.Name, Lookat:=xlWhole, MatchCase:=False)


      If Not r Is Nothing Then
         Sheets("ES").Cells(Val(lstMyData.List(lstMyData.ListIndex, 0)), r.Column).Value = ctl.Value
      End If
   Next


End Sub

Display More

2 additional comments:
The maximum number of columns in an unbound list control is 10. That is usually more than adequate.
You should consider changing your naming convention for the controls to reflect the data stored in them. For example, Item - txtItem rather than Control1. Makes it a lot easier to see what's related to what.

Only suggesting such a radical change as using the bound control will cause more and more issues as the code evolves...

Attached workbook incorporates those changes - only a couple of controls defined on Map sheet, just to test.

daren.beaney · Aug 13th 2015

Re: UserForm - Updating active record from user form.

This is amazing! Thanks for your expert help on this. It makes total sense to do it this way rather than the over complicated and limited bound controls. I've renamed the controls as you mentioned, which does make it easier to identify what they relate to. I can manage with the limitation of 10 columns.

Thanks again for all your help!

Updating record from Userform

Participate now!

Share