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

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
187
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
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



Code:
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

Code:
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
 
Solution

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
187
Office Version
  1. 2016
Platform
  1. Windows
hi Dave,

actually I faced problem in your updating I put this

VBA Code:
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

Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,259
Messages
5,745,719
Members
423,969
Latest member
seanguerrero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top