VBA Find value and send corresponding row values to text boxes but from multiple sheets

[Melimob]

Hi there

After searching around tutorials I've managed to piece together the below but am now at a standstill with the next 'phase':

I have a mutlipage userform which relate to various worksheets. 'Valuations' 'Listings' 'Sales' 'Exchanges'.
There are some constant fields across all worksheets and therefore userform pages such as property address fields and vendor.
User would add entry beginning with 'Valuations' and this would create a unique id.

The idea is this record would then be added to Listings then Sales when/if appropriate etc.. depending on the life cycle but would always start with Valuation so would link together by the unique id across sheets.

If user wants to update or add that property to any other page they click search and select from listbox.
Using the Unique ID as the key, I've successfully got the 'static' data to populate into all the different textboxes on various multipages but now I'm stuck on a few things and am unsure how to handle:

1) if user searches and selects, pre-populated 'constant' fields should be disabled for editing (i.e. read only).

2) if user searches and selects, should check if that ID already exists on each sheet and
If does exist: populate the 'extra' fields on relevant page
If doesn't exist: fields should be blank so that user can enter (and I will get it to transfer via an 'add/transfer command'.

(e.g. page/worksheet of Listings has taken static property address etc from Valuations sheet but it could have been added to listings already and have the extra information, if so, should be populated to listing userform so the user knows it's in Listings already and can update if need be, if not in listings yet, 'extra' fields are blank for user to enter).

I've probably over complicated this so will show my code (everything in comments are the 'extra' textboxes that user will need to complete (if not record exists on 'specified sheet' or edit 'if record exists'. Ignore the code for comments but just helped me to see what fields I have to consider:

Private Sub lstSearch_Click()


'dim the variables
Dim i As Integer
Dim x As Integer
Dim ws As Worksheet
Dim wsLR As Long






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


'send UID to datasheet


Sheets("Valuations").Range("v6").Value = Me.lstSearch.Column(0, i)


Set ws = ThisWorkbook.Sheets("Valuations")


 wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row






For x = 13 To wsLR


    If ws.Cells(x, 1) = Sheets("Valuations").Range("v6").Value Then
        
        'get stats for Valuations page
        
        Me.cbxOfficeVal = ws.Cells(x, "B")
        Me.tbDateVal = ws.Cells(x, "C")
        Me.cbxValuer = ws.Cells(x, "D")
        Me.chbValLetter = ws.Cells(x, "K")
        Me.tbVendorVal = ws.Cells(x, "I")
        Me.tbHouseVal = ws.Cells(x, "E")
        Me.tbStreetVal = ws.Cells(x, "F")
        Me.tbCityVal = ws.Cells(x, "G")
        Me.tbPostCodeVal = ws.Cells(x, "H")
        Me.tbValueAmountVal = ws.Cells(x, "J")
        Me.cbxEnqSourceVal = ws.Cells(x, "L")
        Me.cbxDataSourceVal = ws.Cells(x, "M")
        Me.tbNotesVal = ws.Cells(x, "N")
    
    'get stats for Listings page
        Me.cbxOfficeList = ws.Cells(x, "B")
        'Me.tbDateList = ws.Cells(x, "C")
        'Me.cbxLister = ws.Cells(x, "D")
        'Me.chbBoardList = ws.Cells(x, "K")
        'Me.chbPM2 = ws.Cells(x, "K")
        Me.tbVendorList = ws.Cells(x, "I")
        Me.tbHouseList = ws.Cells(x, "E")
        Me.tbStreetList = ws.Cells(x, "F")
        Me.tbCityList = ws.Cells(x, "G")
        Me.tbPostCodeList = ws.Cells(x, "H")
        Me.tbValAmntList = ws.Cells(x, "J")
        'Me.tbPriceList = ws.Cells(x, "J")
        'Me.tbFeeList = ws.Cells(x, "J")
        'Me.cbxStatusList = ws.Cells(x, "L")
        'Me.tbNotesList = ws.Cells(x, "M")
        
   'get stats for Sales page
        
         Me.cbxOffSales = ws.Cells(x, "B")
        'Me.tbDateSales = ws.Cells(x, "C")
        'Me.cbxNegSales = ws.Cells(x, "D")
        'Me.chbSale = ws.Cells(x, "K")
        'Me.chbAbort = ws.Cells(x, "K")
        Me.tbVendorSales = ws.Cells(x, "I")
        'Me.tbPurchaserSales = ws.Cells(x, "K")
        Me.tbHouseSales = ws.Cells(x, "E")
        Me.tbStreetSales = ws.Cells(x, "F")
        Me.tbCitySales = ws.Cells(x, "G")
        Me.tbPostCodeSales = ws.Cells(x, "H")
        Me.tbValueAmntSales = ws.Cells(x, "J")
        'Me.tbPriceSales = ws.Cells(x, "J")
        'Me.tbFeeSales = ws.Cells(x, "J")
        'Me.tbNotesSales = ws.Cells(x, "M")
        
    'get stats for Exchanges page
        
        Me.cbxOffDtlExc = ws.Cells(x, "B")
        'Me.tbDateExc = ws.Cells(x, "C")
        'Me.tbDateCompExc = ws.Cells(x, "C")
        'Me.tbDatePaidExc = ws.Cells(x, "C")
        'me.chbOffSplitExc = ws.Cells(x, "C")
        'me.chbIntheBookExc = ws.Cells(x, "C")
        'Me.tbListByExc = ws.Cells(x, "C") (from Listings worksheet)
        'Me.tbInvNoExc = ws.Cells(x, "C")
        'Me.tbSoldByExc = ws.Cells(x, "C")(from Sales worksheet)
        'Me.tbInvAmntExc = ws.Cells(x, "C")
        Me.tbVendorExc = ws.Cells(x, "I")
        'Me.tbPurchaserExc = ws.Cells(x, "K") (from Sales worksheet)
        Me.tbHouseExc = ws.Cells(x, "E")
        Me.tbStreetExc = ws.Cells(x, "F")
        Me.tbCityExc = ws.Cells(x, "G")
        Me.tbPostCodeExc = ws.Cells(x, "H")
        Me.tbValAmntExc = ws.Cells(x, "J")
        'Me.tbPriceExc = ws.Cells(x, "J") (from sales worksheet)
        'Me.tbFeeExc = ws.Cells(x, "J")
        'Me.cbxUpdateListStatusExc (should show list status from listings worksheet and if changed edit listings worksheet with new)
        
    
    Exit Sub
    
    End If


Next x


            
End Sub

Many thanks in advance!