Display UserForm with information from another worksheet

Mania112

New Member
Joined
Jul 30, 2018
Messages
2
I have some code which displays a userform with customer address and personal information when I click on their surname.

The problem is that my 'list' worksheet may need to be filtered and so the row order may not match the 'data' worksheet.

Therefore, I need to edit the code so that it doesn't simply display cells from the matching row, but instead finds the row with a matching customerID reference in both sheets?

Thank you

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Target.Count = 1 Then
   
      If Target.Column = 2 And Target <> "" Then
                    
        UserFormCustomerDetails.Label1 = _
            Worksheets("Data").Cells(Target.Row, "AB") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AC") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AD") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AE") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AF") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AG") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AH") & vbCrLf
            
        UserFormCustomerDetails.Label2 = _
            Worksheets("Data").Cells(Target.Row, "V") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AL") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AM") & vbCrLf & _
            Worksheets("Data").Cells(Target.Row, "AN") & vbCrLf
         UserFormCustomerDetails.Show
      
      Else
      
         UserFormCustomerDetails.Hide
         
      End If

   End If

End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
this incorporates an index column

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MyIndex As Long
    ' place the CustID values in column "AP"
    If Target.Count = 1 Then
   
        If Target.Column = 2 And Target <> "" Then

            With Worksheets("Data")
                MyIndex = .Cells(Target.Row, "AP")
                UserFormCustomerDetails.Label1 = _
                    .Cells(MyIndex, "AB") & vbCrLf & _
                    .Cells(MyIndex, "AC") & vbCrLf & _
                    .Cells(MyIndex, "AD") & vbCrLf & _
                    .Cells(MyIndex, "AE") & vbCrLf & _
                    .Cells(MyIndex, "AF") & vbCrLf & _
                    .Cells(MyIndex, "AG") & vbCrLf & _
                    .Cells(MyIndex, "AH") & vbCrLf
               
                UserFormCustomerDetails.Label2 = _
                    .Cells(MyIndex, "V") & vbCrLf & _
                    .Cells(MyIndex, "AL") & vbCrLf & _
                    .Cells(MyIndex, "AM") & vbCrLf & _
                    .Cells(MyIndex, "AN") & vbCrLf
                UserFormCustomerDetails.Show
            End With
       
        Else
            UserFormCustomerDetails.Hide
       
        End If
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,063
Members
416,010
Latest member
NJT

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