Hi, I hope I am going to be able to explain this and apologies in advance for my horrendous code but am new to all this..
ok so i have a listbox on the top part of the userform which shows search results from Valuations worksheet.
in the bottom part of the userform there are multipages representing 4 worksheets; Valuations, Listings, Sales, Exchanges.
All records start on Valuations worksheet and could be on the Listings, Sales, Exchanges all tied together with a Unique ID.
Currently user selects a row in the listbox and the below code should:
1) populate all textboxes across all 4 pages with the 'constant data' shown as 'x' *This part works fine*
what doesn't currently work is the 'y, z, a' or else part..
2) it should look for the UID in the other 3 worksheets and if found, return the row/column data to the relevant multipage textbox as mapped out below.
3) if it doesn't find the UID on the other worksheets, it should just leave those textboxes blank ready for input
I did get point 2 to work before I added the else but with the 'else' 2 nor 3 seems to work?
Many thanks in advance > this has taken me hourssssssssssssss!
any advice appreciated!
ok so i have a listbox on the top part of the userform which shows search results from Valuations worksheet.
in the bottom part of the userform there are multipages representing 4 worksheets; Valuations, Listings, Sales, Exchanges.
All records start on Valuations worksheet and could be on the Listings, Sales, Exchanges all tied together with a Unique ID.
Currently user selects a row in the listbox and the below code should:
1) populate all textboxes across all 4 pages with the 'constant data' shown as 'x' *This part works fine*
what doesn't currently work is the 'y, z, a' or else part..
2) it should look for the UID in the other 3 worksheets and if found, return the row/column data to the relevant multipage textbox as mapped out below.
3) if it doesn't find the UID on the other worksheets, it should just leave those textboxes blank ready for input
I did get point 2 to work before I added the else but with the 'else' 2 nor 3 seems to work?
Many thanks in advance > this has taken me hourssssssssssssss!
any advice appreciated!
Code:
Private Sub lstSearch_Click()
'dim the variables
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim a As Integer
Dim ws As Worksheet
Dim wsl As Worksheet
Dim wss As Worksheet
Dim wse 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")
Set wsl = ThisWorkbook.Sheets("Listings")
Set wss = ThisWorkbook.Sheets("Sales")
Set wse = ThisWorkbook.Sheets("Exchanges")
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
'constants from Valuations sheets sent to all 4 multipages
Me.tbUIDVal = ws.Cells(x, "A").Text
Me.tbUIDList = ws.Cells(x, "A").Text
Me.tbUIDSales = ws.Cells(x, "A").Text
Me.tbUIDExc = ws.Cells(x, "A").Text
Me.cbxOfficeVal = ws.Cells(x, "B")
Me.cbxOfficeList = ws.Cells(x, "B")
Me.cbxOffSales = ws.Cells(x, "B")
Me.cbxOffDtlExc = ws.Cells(x, "B")
Me.tbHouseVal = ws.Cells(x, "E")
Me.tbHouseList = ws.Cells(x, "E")
Me.tbHouseSales = ws.Cells(x, "E")
Me.tbHouseExc = ws.Cells(x, "E")
Me.tbStreetVal = ws.Cells(x, "F")
Me.tbStreetList = ws.Cells(x, "F")
Me.tbStreetSales = ws.Cells(x, "F")
Me.tbStreetExc = ws.Cells(x, "F")
Me.tbCityVal = ws.Cells(x, "G")
Me.tbCityList = ws.Cells(x, "G")
Me.tbCitySales = ws.Cells(x, "G")
Me.tbCityExc = ws.Cells(x, "G")
Me.tbPostCodeVal = ws.Cells(x, "H")
Me.tbPostCodeList = ws.Cells(x, "H")
Me.tbPostCodeSales = ws.Cells(x, "H")
Me.tbPostCodeExc = ws.Cells(x, "H")
Me.tbValueAmountVal = ws.Cells(x, "J")
Me.tbValAmntList = ws.Cells(x, "J")
Me.tbValueAmntSales = ws.Cells(x, "J")
Me.tbValAmntExc = ws.Cells(x, "J")
Me.tbVendorVal = ws.Cells(x, "I")
Me.tbVendorList = ws.Cells(x, "I")
Me.tbVendorSales = ws.Cells(x, "I")
Me.tbVendorExc = ws.Cells(x, "I")
'cells on valuations worksheet that should only be sent to Valuations multipage
Me.tbDateVal = ws.Cells(x, "C")
Me.cbxValuer = ws.Cells(x, "D")
Me.chbValLetter = ws.Cells(x, "K")
Me.cbxEnqSourceVal = ws.Cells(x, "L")
Me.cbxDataSourceVal = ws.Cells(x, "M")
Me.tbNotesVal = ws.Cells(x, "N")
End If
Next x
For y = 13 To wsLR
If wsl.Cells(y, 1) = Sheets("Valuations").Range("v6").Value Then
'get stats from Listings worksheet for addtional fields to add to listings page if record found
Me.tbDateList = wsl.Cells(y, "C")
Me.cbxLister = wsl.Cells(y, "D")
Me.chbBoardList = wsl.Cells(y, "M")
Me.chbPM2 = wsl.Cells(y, "N")
Me.tbPriceList = wsl.Cells(y, "J")
Me.tbPriceSales = ws.Cells(y, "J")
Me.tbFeeList = wsl.Cells(y, "K")
Me.cbxStatusList = wsl.Cells(y, "O")
Me.tbNotesList = wsl.Cells(y, "L")
'Comes from Listing worksheet to Exchange Multipage
Me.tbPriceExc = wsl.Cells(y, "J")
Me.cbxUpdateListStatusExc = wsl.Cells(y, "O")
Else
Me.tbDateList = ""
Me.cbxLister = ""
Me.chbBoardList = ""
Me.chbPM2 = ""
Me.tbPriceList = ""
Me.tbPriceSales = ""
Me.tbFeeList = ""
Me.cbxStatusList = ""
Me.tbNotesList = ""
Me.tbPriceExc = ""
Me.cbxUpdateListStatusExc = ""
End If
Next y
For z = 13 To wsLR
If wss.Cells(z, 1) = Sheets("Valuations").Range("v6").Value Then
'get stats from Sales worksheet for addtional fields to add to Sales page if record found
Me.tbDateSales = wss.Cells(z, "C")
Me.cbxNegSales = wss.Cells(z, "D")
Me.chbSale = wss.Cells(z, "K")
Me.chbAbort = wss.Cells(z, "K")
Me.tbPurchaserSales = wss.Cells(z, "I")
Me.tbFeeSales = wss.Cells(z, "J")
Me.tbNotesSales = wss.Cells(z, "L")
'comes from Sales worksheet to Exchange multipage
Me.tbPurchaserExc = wss.Cells(z, "I")
Else
Me.tbDateSales = ""
Me.cbxNegSales = ""
Me.chbSale = ""
Me.chbAbort = ""
Me.tbPurchaserSales = ""
Me.tbFeeSales = ""
Me.tbNotesSales = ""
Me.tbPurchaserExc = ""
End If
Next z
For a = 13 To wsLR
If wse.Cells(a, 1) = Sheets("Valuations").Range("v6").Value Then
Me.tbDateExc = wse.Cells(a, "C")
Me.tbDateCompExc = wse.Cells(a, "K")
Me.tbDatePaidExc = wse.Cells(a, "L")
Me.chbOffSplitExc = wse.Cells(a, "N")
Me.chbIntheBookExc = wse.Cells(a, "M")
Me.tbListByExc = wse.Cells(a, "H")
Me.tbSoldByExc = wse.Cells(a, "I")
Me.tbFeeExc = wse.Cells(a, "J")
Else
Me.tbDateExc = ""
Me.tbDateCompExc = ""
Me.tbDatePaidExc = ""
Me.chbOffSplitExc = ""
Me.chbIntheBookExc = ""
Me.tbListByExc = ""
Me.tbSoldByExc = ""
Me.tbFeeExc = ""
End If
Next a
Exit Sub
End Sub
Last edited: