modified code "Akuini" populate data in textboxes and comboboxes after matching

[KalilMe]

hello
in earlier time i got help from @Akuini in this thread using loop to populate data in textboxes and comboboxes after matching to populate data from sheet to multiple controls(comboboxes and textboxes) . now I would mod this code by add command button to copy data from userform to sheet

Sub toPopulate()

  Dim c As Range
  Dim tx As String
  Dim i As Long, j As Long, k As Long, m As Long
 
    tx = Me.ComboBoxSearch.Value
        ws.Cells(1).CurrentRegion.Sort Key1:=ws.Cells(1), Order1:=xlAscending, Header:=xlYes
      
        Set c = ws.Columns(1).Find(What:=tx, LookIn:=xlValues, lookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
        k = WorksheetFunction.CountIf(ws.Columns(1), tx)
        If k > nSet Then MsgBox "Too many invoices. You need more textbox & combobox": Exit Sub
          For j = 1 To k
                For i = 1 To 6
                    Me.Controls(ary(i + m)).Value = c.Offset(j - 1, i - 1).Value
                Next
                m = m + 6
          Next
        End If
End Sub

thanks in advance

 

[dmt32]

Hi,

If your code returns from your worksheet, data to the correct controls for editing and you want to return the updated record back to the worksheet then in theory, all you need do is to reverse the operation in the same code - MAYBE!


Not tested an only an idea but try this modification to your code & see if does what you want

Sub toPopulate(Optional SaveRecord As XlSaveAction)
    
    Dim c           As Range
    Dim tx          As String
    Dim i           As Long, j As Long, k As Long, m As Long
    
    tx = Me.ComboBoxSearch.Value
    ws.Cells(1).CurrentRegion.Sort Key1:=ws.Cells(1), Order1:=xlAscending, Header:=xlYes
    
    Set c = ws.Columns(1).Find(What:=tx, LookIn:=xlValues, lookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        k = WorksheetFunction.CountIf(ws.Columns(1), tx)
        If k > nSet Then MsgBox "Too many invoices. You need more textbox & combobox": Exit Sub
        For j = 1 To k
            For i = 1 To 6
                With Me.Controls(ary(i + m))
                    If SaveRecord = xlSaveChanges Then
                        c.Offset(j - 1, i - 1).Value = .Value
                    Else
                        .Value = c.Offset(j - 1, i - 1).Text
                    End If
                End With
            Next
            m = m + 6
        Next
    End If
    
End Sub

To save the record back apply the optional argument as follows

toPopulate xlSaveChanges

If solution does not do what is required then perhaps the author of your code will be able to offer you some further guidance.

Dave

 

[KalilMe]

hi Dave,

actually I faced problem in your updating I put this

toPopulate xlSaveChanges

in the end of the code then it doesn't stop the code from running and swings , but when I put theses in command button

Private Sub CommandButton1_Click()
toPopulate xlSaveChanges
toPopulate
end sub

it works perfectly. actually I see in many your posts which you contribute in solution are efficient , great . you are really professional

thanks so much for your assistance

 

[dmt32]

it works perfectly. actually I see in many your posts which you contribute in solution are efficient , great . you are really professional

thanks so much for your assistance

Glad the suggestion does what you want & many thanks for your kind words, very much appreciated

Dave