Hello All I currently have a combo box using the following code:
and a save button with this code
The combo box uses a Location Ref from the sheet 'Full List' as the info provided in the drop down, but this has now become to large to manage and I need a way of filtering down the data.I have a a number of fields that I can filter with, I just Have no Idea where to start with this one. My preference would be for either radio button filters or multiple Combo boxes. I need to keep the read and write to functions as is if possible. Any help greatfully recieved.
Thanks
Jaysteruk
Code:
Sub DropDown4_Change()
Dim LProject_Ref As Integer
Dim Lstair_Planned_Date_1 As String
Dim Lstair_Planned_Date_2 As String
Dim Lstair_PLanned_Date_3 As String
Dim Lstair_Planned_Date_4 As String
Dim Lstair_actual_Date_1 As String
Dim Lstair_actual_Date_2 As String
Dim Lstair_actual_Date_3 As String
Dim Lstair_actual_Date_4 As String
Dim LElderly_Alarms_planned_1 As String
Dim LElderly_Alarms_planned_2 As String
Dim LElderly_Alarms_planned_3 As String
Dim LElderly_Alarms_planned_4 As String
Dim LElderly_Alarms_actual_1 As String
Dim LElderly_Alarms_actual_2 As String
Dim LElderly_Alarms_actual_3 As String
Dim LElderly_Alarms_actual_4 As String
Dim LFire_Alarms_planned_1 As String
Dim LFire_Alarms_Planned_2 As String
Dim LFire_Alarms_Planned_3 As String
Dim LFire_Alarms_Planned_4 As String
Dim LFire_Alarms_actual_1 As String
Dim LFire_Alarms_actual_2 As String
Dim LFire_Alarms_actual_3 As String
Dim LFire_Alarms_actual_4 As String
Dim LFire_equipment_planned_1 As String
Dim LFire_equipment_planned_2 As String
Dim LFire_equipment_planned_3 As String
Dim LFire_equipment_planned_4 As String
Dim LFire_equipment_actual_1 As String
Dim LFire_equipment_actual_2 As String
Dim LFire_equipment_actual_3 As String
Dim LFire_equipment_actual_4 As String
Dim LEmerg_Lighting_planned_1 As String
Dim LEmerg_Lighting_planned_2 As String
Dim LEmerg_Lighting_Planned_3 As String
Dim LEmerg_Lighting_planned_4 As String
Dim LEmerg_Lighting_actual_1 As String
Dim LEmerg_Lighting_actual_2 As String
Dim LEmerg_Lighting_actual_3 As String
Dim LEmerg_Lighting_actual_4 As String
Dim LPat_test_planned_1 As String
Dim LPat_test_planned_2 As String
Dim LPat_test_planned_3 As String
Dim LPat_test_planned_4 As String
Dim LPat_test_actual_1 As String
Dim LPat_test_actual_2 As String
Dim LPat_test_actual_3 As String
Dim LPat_test_actual_4 As String
Dim Lbed_hoist_planned_1 As String
Dim Lbed_hoist_planned_2 As String
Dim Lbed_hoist_planned_3 As String
Dim Lbed_hoist_planned_4 As String
Dim Lbed_hoist_actual_1 As String
Dim Lbed_hoist_actual_2 As String
Dim Lbed_hoist_actual_3 As String
Dim Lbed_hoist_actual_4 As String
Dim Lin_block_lift_planned_1 As String
Dim Lin_block_lift_planned_2 As String
Dim Lin_block_lift_planned_3 As String
Dim Lin_block_lift_planned_4 As String
Dim Lin_block_lift_actual_1 As String
Dim Lin_block_lift_actual_2 As String
Dim Lin_block_lift_actual_3 As String
Dim Lin_block_lift_actual_4 As String
Dim LMisc_planned_1 As String
Dim LMisc_planned_2 As String
Dim LMisc_actual_1 As String
Dim LMisc_actual_2 As String
Dim LRow As Long
Dim LFound As Boolean
'Retrieve Project_Ref number number
LProject_Ref = Range("A8").Value
'Move to Full List
Sheets("Full List").Select
LFound = False
LRow = 2
Do While LFound = False
'Found matching Project_Ref, now update information on Date Entry
If Range("A" & LRow).Value = LProject_Ref Then
LFound = True
Lstair_Planned_Date_1 = Range("J" & LRow).Value
Lstair_Planned_Date_2 = Range("K" & LRow).Value
Lstair_PLanned_Date_3 = Range("L" & LRow).Value
Lstair_Planned_Date_4 = Range("M" & LRow).Value
Lstair_actual_Date_1 = Range("N" & LRow).Value
Lstair_actual_Date_2 = Range("O" & LRow).Value
Lstair_actual_Date_3 = Range("P" & LRow).Value
Lstair_actual_Date_4 = Range("Q" & LRow).Value
LElderly_Alarms_planned_1 = Range("Z" & LRow).Value
LElderly_Alarms_planned_2 = Range("AA" & LRow).Value
LElderly_Alarms_planned_3 = Range("AB" & LRow).Value
LElderly_Alarms_planned_4 = Range("AC" & LRow).Value
LElderly_Alarms_actual_1 = Range("AD" & LRow).Value
LElderly_Alarms_actual_2 = Range("AE" & LRow).Value
LElderly_Alarms_actual_3 = Range("AF" & LRow).Value
LElderly_Alarms_actual_4 = Range("AG" & LRow).Value
LFire_Alarms_planned_1 = Range("DH" & LRow).Value
LFire_Alarms_Planned_2 = Range("DI" & LRow).Value
LFire_Alarms_Planned_3 = Range("DJ" & LRow).Value
LFire_Alarms_Planned_4 = Range("DK" & LRow).Value
LFire_Alarms_actual_1 = Range("DL" & LRow).Value
LFire_Alarms_actual_2 = Range("DM" & LRow).Value
LFire_Alarms_actual_3 = Range("DN" & LRow).Value
LFire_Alarms_actual_4 = Range("DO" & LRow).Value
LFire_equipment_planned_1 = Range("CQ" & LRow).Value
LFire_equipment_planned_2 = Range("CR" & LRow).Value
LFire_equipment_planned_3 = Range("CS" & LRow).Value
LFire_equipment_planned_4 = Range("CT" & LRow).Value
LFire_equipment_actual_1 = Range("CU" & LRow).Value
LFire_equipment_actual_2 = Range("CV" & LRow).Value
LFire_equipment_actual_3 = Range("CW" & LRow).Value
LFire_equipment_actual_4 = Range("CX" & LRow).Value
LEmerg_Lighting_planned_1 = Range("EX" & LRow).Value
LEmerg_Lighting_planned_2 = Range("EY" & LRow).Value
LEmerg_Lighting_Planned_3 = Range("EZ" & LRow).Value
LEmerg_Lighting_planned_4 = Range("FA" & LRow).Value
LEmerg_Lighting_actual_1 = Range("FB" & LRow).Value
LEmerg_Lighting_actual_2 = Range("FC" & LRow).Value
LEmerg_Lighting_actual_3 = Range("FD" & LRow).Value
LEmerg_Lighting_actual_4 = Range("FE" & LRow).Value
LPat_test_planned_1 = Range("BI" & LRow).Value
LPat_test_planned_2 = Range("BJ" & LRow).Value
LPat_test_planned_3 = Range("BK" & LRow).Value
LPat_test_planned_4 = Range("BL" & LRow).Value
LPat_test_actual_1 = Range("BM" & LRow).Value
LPat_test_actual_2 = Range("BN" & LRow).Value
LPat_test_actual_3 = Range("BO" & LRow).Value
LPat_test_actual_4 = Range("BP" & LRow).Value
Lbed_hoist_planned_1 = Range("AS" & LRow).Value
Lbed_hoist_planned_2 = Range("AT" & LRow).Value
Lbed_hoist_planned_3 = Range("AU" & LRow).Value
Lbed_hoist_planned_4 = Range("AV" & LRow).Value
Lbed_hoist_actual_1 = Range("AW" & LRow).Value
Lbed_hoist_actual_2 = Range("AX" & LRow).Value
Lbed_hoist_actual_3 = Range("AY" & LRow).Value
Lbed_hoist_actual_4 = Range("AZ" & LRow).Value
Lin_block_lift_planned_1 = Range("BZ" & LRow).Value
Lin_block_lift_planned_2 = Range("CA" & LRow).Value
Lin_block_lift_planned_3 = Range("CB" & LRow).Value
Lin_block_lift_planned_4 = Range("CC" & LRow).Value
Lin_block_lift_actual_1 = Range("CD" & LRow).Value
Lin_block_lift_actual_2 = Range("CE" & LRow).Value
Lin_block_lift_actual_3 = Range("CF" & LRow).Value
Lin_block_lift_actual_4 = Range("CG" & LRow).Value
LMisc_planned_1 = Range("EG" & LRow).Value
LMisc_planned_2 = Range("EG" & LRow).Value
LMisc_actual_1 = Range("EG" & LRow).Value
LMisc_actual_2 = Range("EG" & LRow).Value
Sheets("Date Entry").Select
Range("D14").Value = Lstair_Planned_Date_1
Range("G14").Value = Lstair_Planned_Date_2
Range("J14").Value = Lstair_PLanned_Date_3
Range("M14").Value = Lstair_Planned_Date_4
Range("D16").Value = Lstair_actual_Date_1
Range("G16").Value = Lstair_actual_Date_2
Range("J16").Value = Lstair_actual_Date_3
Range("M16").Value = Lstair_actual_Date_4
Range("D50").Value = Lelderly_alarm_planned_1
Range("G50").Value = Lelderly_alarm_planned_2
Range("J50").Value = Lelderly_alarm_planned_3
Range("M50").Value = Lelderly_alarm_planned_4
Range("D52").Value = Lelderly_alarm_actual_1
Range("G52").Value = Lelderly_alarm_actual_2
Range("J52").Value = Lelderly_alarm_actual_3
Range("M52").Value = Lelderly_alarm_actual_4
Range("D20").Value = LFire_Alarms_planned_1
Range("G20").Value = LFire_Alarms_Planned_2
Range("J20").Value = LFire_Alarms_Planned_3
Range("M20").Value = LFire_Alarms_Planned_4
Range("D22").Value = LFire_Alarms_actual_1
Range("G22").Value = LFire_Alarms_actual_2
Range("J22").Value = LFire_Alarms_actual_3
Range("M22").Value = LFire_Alarms_actual_4
Range("D26").Value = LFire_equipment_planned_1
Range("G26").Value = LFire_equipment_planned_2
Range("J26").Value = LFire_equipment_planend_3
Range("M26").Value = LFire_equipment_planned_4
Range("D28").Value = LFire_equipment_actual_1
Range("G28").Value = LFire_equipment_actual_2
Range("J28").Value = LFire_equipment_actual_3
Range("M28").Value = LFire_equipment_actual_4
Range("D32").Value = LEmerg_Lighting_planned_1
Range("G32").Value = LEmerg_Lighting_planned_2
Range("J32").Value = LEmerg_Lighting_Planned_3
Range("M32").Value = LEmerg_Lighting_planned_4
Range("D34").Value = LEmerg_Lighting_actual_1
Range("G34").Value = LEmerg_Lighting_actual_2
Range("J34").Value = LEmerg_Lighting_actual_3
Range("M34").Value = LEmerg_Lighting_actual_4
Range("D38").Value = LPat_test_planned_1
Range("G38").Value = LPat_test_planned_2
Range("J38").Value = LPat_test_planned_3
Range("M38").Value = LPat_test_planned_4
Range("D40").Value = LPat_test_actual_1
Range("G40").Value = LPat_test_actual_2
Range("J40").Value = LPat_test_actual_3
Range("M40").Value = LPat_test_actual_4
Range("D56").Value = Lbed_hoist_planned_1
Range("G56").Value = Lbed_hoist_planned_2
Range("J56").Value = Lbed_hoist_planned_3
Range("M56").Value = Lbed_hoist_planned_4
Range("D58").Value = Lbed_hoist_actual_1
Range("G58").Value = Lbed_hoist_acutal_2
Range("J58").Value = Lbed_hoist_actual_3
Range("M58").Value = Lbed_hoist_actual_4
Range("D44").Value = Lin_block_lift_planned_1
Range("G44").Value = Lin_block_lift_planned_2
Range("J44").Value = Lin_block_lift_planned_3
Range("M44").Value = Lin_block_lift_planned_4
Range("D46").Value = Lin_block_lift_actual_1
Range("G46").Value = Lin_block_lift_actual_2
Range("J46").Value = Lin_block_lift_actual_3
Range("M46").Value = Lin_block_lift_actual_4
Range("D62").Value = LMisc_planned_1
Range("G62").Value = LMisc_planned_2
Range("D64").Value = LMisc_actual_1
Range("G64").Value = LMisc_actual_2
'Encountered a blank project_ref number (assuming end of list on full list)
ElseIf IsEmpty(Range("A" & LRow).Value) = True Then
MsgBox ("No Match was Found for Combo box Selection.")
Exit Sub
End If
LRow = LRow + 1
Loop
End Suband a save button with this code
Code:
Sub Button3_Click()
'Update data on Full List based on changes made to data on Invoice Control
Dim LProject_Ref As Integer
Dim Lstair_lift_invoice_1 As String
Dim Lstair_lift_invoice_2 As String
Dim Lstair_lift_invoice_3 As String
Dim Lstair_lift_invoice_4 As String
Dim Lstair_lift_planned_date_1 As String
Dim Lstair_lift_planned_date_2 As String
Dim Lstair_lift_planned_date_3 As String
Dim Lstair_lift_planned_date_4 As String
Dim Lstair_lift_actual_date_1 As String
Dim Lstair_lift_actual_date_2 As String
Dim Lstair_lift_actual_date_3 As String
Dim Lstair_lift_actual_date_4 As String
Dim Lstair_Additional_Information As String
Dim LElderly_Alarms_invoice_1 As String
Dim LElderly_Alarms_invoice_2 As String
Dim LElderly_Alarms_invoice_3 As String
Dim LElderly_Alarms_invoice_4 As String
Dim Leam_actual_date_1 As String
Dim Leam_actual_date_2 As String
Dim Leam_actual_date_3 As String
Dim Leam_actual_date_4 As String
Dim Leam_planned1 As String
Dim Leam_planned2 As String
Dim Leam_planned3 As String
Dim Leam_planned4 As String
Dim Leam_notes As String
Dim LFire_Alarms_1 As String
Dim LFire_Alarms_2 As String
Dim LFire_Alarms_3 As String
Dim LFire_Alarms_4 As String
Dim LFire_Alarms_actual1 As String
Dim LFire_Alarms_actual2 As String
Dim LFire_Alarms_actual3 As String
Dim LFire_Alarms_actual4 As String
Dim LFire_Alarms_planned1 As String
Dim LFire_Alarms_planned2 As String
Dim LFire_Alarms_planned3 As String
Dim LFire_Alarms_planned4 As String
Dim LFire_Alarms_Notes As String
Dim LFire_equipment_1 As String
Dim LFire_equipment_2 As String
Dim LFire_equipment_3 As String
Dim LFire_equipment_4 As String
Dim LFire_equip_planned1 As String
Dim LFire_equip_actual1 As String
Dim LFire_equip_notes As String
Dim LEmerg_Lighting_1 As String
Dim LEmerg_Lighting_2 As String
Dim LEmerg_Lighting_3 As String
Dim LEmerg_Lighting_4 As String
Dim LEmerg_lights_actual1 As String
Dim LEmerg_lights_actual2 As String
Dim LEmerg_lights_actual3 As String
Dim LEmerg_lights_actual4 As String
Dim Lemerg_lights_plan1 As String
Dim Lemerg_lights_plan2 As String
Dim Lemerg_lights_plan3 As String
Dim Lemerg_lights_plan4 As String
Dim Lemerg_lights_notes As String
Dim LPat_test_1 As String
Dim LPat_test_2 As String
Dim LPat_test_3 As String
Dim LPat_test_4 As String
Dim LPat_test_actual1 As String
Dim LPat_test_plan1 As String
Dim LPat_test_notes As String
Dim Lbed_hoist_1 As String
Dim Lbed_hoist_2 As String
Dim Lbed_hoist_3 As String
Dim Lbed_hoist_4 As String
Dim Lbhb_actual1 As String
Dim Lbhb_actual2 As String
Dim Lbhb_actual3 As String
Dim Lbhb_actual4 As String
Dim Lbhb_plan1 As String
Dim Lbhb_plan2 As String
Dim Lbhb_plan3 As String
Dim Lbhb_plan4 As String
Dim Lbhb_notes As String
Dim Lin_block_lift_1 As String
Dim Lin_block_lift_2 As String
Dim Lin_block_lift_3 As String
Dim Lin_block_lift_4 As String
Dim Lin_block_actual1 As String
Dim Lin_block_actual2 As String
Dim Lin_block_actual3 As String
Dim Lin_block_actual4 As String
Dim Lin_block_actual5 As String
Dim Lin_block_actual6 As String
Dim Lin_block_plan1 As String
Dim Lin_block_plan2 As String
Dim Lin_block_plan3 As String
Dim Lin_block_plan4 As String
Dim Lin_block_plan5 As String
Dim Lin_block_plan6 As String
Dim Lin_block_notes As String
Dim LChim1 As String
Dim LChim2 As String
Dim LChim_actual1 As String
Dim LChim_actual2 As String
Dim LChim_plan1 As String
Dim LChim_plan2 As String
Dim LChim_notes As String
Dim LCOMBO1 As String
Dim LCOMBO2 As String
Dim LCOMBO_actual1 As String
Dim LCOMBO_actual2 As String
Dim LCOMBO_plan1 As String
Dim LCOMBO_plan2 As String
Dim LCOMBO_notes As String
Dim LGecko1 As String
Dim LGecko2 As String
Dim LGecko_actual1 As String
Dim LGecko_actual2 As String
Dim LGecko_plan1 As String
Dim LGecko_plan2 As String
Dim LGecko_notes As String
Dim LNU As String
Dim LNU2 As String
Dim LNu_actual1 As String
Dim LNu_actual2 As String
Dim LNu_plan1 As String
Dim LNu_plan2 As String
Dim LNu_notes As String
Dim LPump1 As String
Dim LPump2 As String
Dim LPump3 As String
Dim LPump4 As String
Dim LPump_actual1 As String
Dim LPump_actual2 As String
Dim LPump_actual3 As String
Dim LPump_actual4 As String
Dim LPump_plan1 As String
Dim LPump_plan2 As String
Dim LPump_plan3 As String
Dim LPump_plan4 As String
Dim LPump_Notes As String
Dim LSmoke1 As String
Dim LSmoke2 As String
Dim LSmoke3 As String
Dim LSmoke4 As String
Dim LSmoke_actual1 As String
Dim LSmoke_actual2 As String
Dim LSmoke_actual3 As String
Dim LSmoke_actual4 As String
Dim LSmoke_Plan1 As String
Dim LSmoke_Plan2 As String
Dim LSmoke_Plan3 As String
Dim LSmoke_Plan4 As String
Dim LSmoke_notes As String
Dim LRow As Long
Dim LFound As Boolean
'Retrieve Project_ref number number
LProject_Ref = Range("a8").Value
'Retrieve new information
Lstair_lift_invoice_1 = Range("D14").Value
Lstair_lift_invoice_2 = Range("G14").Value
Lstair_lift_invoice_3 = Range("J14").Value
Lstair_lift_invoice_4 = Range("M14").Value
Lstair_lift_planned_date_1 = Range("D18").Value
Lstair_lift_planned_date_2 = Range("G18").Value
Lstair_lift_planned_date_3 = Range("J18").Value
Lstair_lift_planned_date_4 = Range("M18").Value
Lstair_lift_actual_date_1 = Range("D19").Value
Lstair_lift_actual_date_2 = Range("G19").Value
Lstair_lift_actual_date_3 = Range("J19").Value
Lstair_lift_actual_date_4 = Range("M19").Value
Lstair_Additional_Information = Range("D21").Value
Lelderly_alarm_invoice_1 = Range("D81").Value
Lelderly_alarm_invoice_2 = Range("G81").Value
Lelderly_alarm_invoice_3 = Range("J81").Value
Lelderly_alarm_invoice_4 = Range("M81").Value
Leam_actual_date_1 = Range("D86").Value
Leam_actual_date_2 = Range("G86").Value
Leam_actual_date_3 = Range("J86").Value
Leam_actual_date_4 = Range("M86").Value
Leam_planned1 = Range("D85").Value
Leam_planned2 = Range("G85").Value
Leam_planned3 = Range("J85").Value
Leam_planned4 = Range("M85").Value
Leam_notes = Range("D88").Value
LFire_Alarms_1 = Range("D25").Value
LFire_Alarms_2 = Range("G25").Value
LFire_Alarms_3 = Range("J25").Value
LFire_Alarms_4 = Range("M25").Value
LFire_Alarms_actual1 = Range("D30").Value
LFire_Alarms_actual2 = Range("G30").Value
LFire_Alarms_actual3 = Range("J30").Value
LFire_Alarms_actual4 = Range("M30").Value
LFire_Alarms_planned1 = Range("D29").Value
LFire_Alarms_planned2 = Range("G29").Value
LFire_Alarms_planned3 = Range("J29").Value
LFire_Alarms_planned4 = Range("M29").Value
LFire_Alarms_Notes = Range("D32").Value
LFire_equipment_1 = Range("D36").Value
LFire_equipment_2 = Range("G36").Value
LFire_equipment_3 = Range("J36").Value
LFire_equipment_4 = Range("M36").Value
LFire_equip_planned1 = Range("D40").Value
LFire_equip_actual1 = Range("D42").Value
LFire_equip_notes = Range("D44").Value
LEmerg_Lighting_1 = Range("D48").Value
LEmerg_Lighting_2 = Range("G48").Value
LEmerg_Lighting_3 = Range("J48").Value
LEmerg_Lighting_4 = Range("M48").Value
LEmerg_lights_actual1 = Range("D53").Value
LEmerg_lights_actual2 = Range("G53").Value
Lemerg_lights_plan1 = Range("D52").Value
Lemerg_lights_plan2 = Range("G52").Value
Lemerg_lights_notes = Range("D55").Value
LPat_test_1 = Range("D59").Value
LPat_test_2 = Range("G59").Value
LPat_test_3 = Range("J59").Value
LPat_test_4 = Range("M59").Value
LPat_test_actual1 = Range("D64").Value
LPat_test_plan1 = Range("D63").Value
LPat_test_notes = Range("D66").Value
Lbed_hoist_1 = Range("D92").Value
Lbed_hoist_2 = Range("G92").Value
Lbed_hoist_3 = Range("J92").Value
Lbed_hoist_4 = Range("M92").Value
Lbhb_actual1 = Range("D97").Value
Lbhb_actual2 = Range("G97").Value
Lbhb_actual3 = Range("J97").Value
Lbhb_actual4 = Range("M97").Value
Lbhb_plan1 = Range("D96").Value
Lbhb_plan2 = Range("G96").Value
Lbhb_plan3 = Range("J96").Value
Lbhb_plan4 = Range("M96").Value
Lbhb_notes = Range("D99").Value
Lin_block_lift_1 = Range("D70").Value
Lin_block_lift_2 = Range("G70").Value
Lin_block_lift_3 = Range("J70").Value
Lin_block_lift_4 = Range("M70").Value
Lin_block_actual1 = Range("D75").Value
Lin_block_actual2 = Range("F75").Value
Lin_block_actual3 = Range("H75").Value
Lin_block_actual4 = Range("J75").Value
Lin_block_actual5 = Range("L75").Value
Lin_block_actual6 = Range("N75").Value
Lin_block_plan1 = Range("D74").Value
Lin_block_plan2 = Range("F74").Value
Lin_block_plan3 = Range("H74").Value
Lin_block_plan4 = Range("J74").Value
Lin_block_plan5 = Range("L74").Value
Lin_block_plan6 = Range("N74").Value
Lin_block_notes = Range("D77").Value
LChim1 = Range("D103").Value
LChim2 = Range("G103").Value
LChim_actual1 = Range("D108").Value
LChim_actual2 = Range("G108").Value
LChim_plan1 = Range("D107").Value
LChim_plan2 = Range("G107").Value
LChim_notes = Range("D110").Value
LCOMBO1 = Range("D114").Value
LCOMBO2 = Range("G114").Value
LCOMBO_actual1 = Range("D119").Value
LCOMBO_plan1 = Range("D118").Value
LCOMBO_notes = Range("D121").Value
LGecko1 = Range("D125").Value
LGecko2 = Range("G125").Value
LGecko_actual1 = Range("D130").Value
LGecko_plan1 = Range("D129").Value
LGecko_notes = Range("D132").Value
LNU = Range("D136").Value
LNU2 = Range("G136").Value
LNu_actual1 = Range("D141").Value
LNu_plan1 = Range("D140").Value
LNu_notes = Range("D143").Value
LPump1 = Range("D147").Value
LPump2 = Range("G147").Value
LPump3 = Range("J147").Value
LPump4 = Range("M147").Value
LPump_actual1 = Range("D152").Value
LPump_actual2 = Range("G152").Value
LPump_actual3 = Range("J152").Value
LPump_actual4 = Range("M152").Value
LPump_plan1 = Range("D151").Value
LPump_plan2 = Range("G151").Value
LPump_plan3 = Range("J151").Value
LPump_plan4 = Range("M151").Value
LPump_Notes = Range("D154").Value
LSmoke1 = Range("D158").Value
LSmoke2 = Range("G158").Value
LSmoke3 = Range("J158").Value
LSmoke4 = Range("M158").Value
LSmoke_actual1 = Range("D163").Value
LSmoke_actual2 = Range("G163").Value
LSmoke_actual3 = Range("J163").Value
LSmoke_actual4 = Range("M163").Value
LSmoke_Plan1 = Range("D162").Value
LSmoke_Plan2 = Range("G162").Value
LSmoke_Plan3 = Range("J162").Value
LSmoke_Plan4 = Range("M162").Value
LSmoke_notes = Range("D165").Value
'Move to Full List to save changes
Sheets("Full List").Select
LFound = False
LRow = 2
Do While LFound = False
'Found matching Project_Ref, now update address and phone number information
If Range("A" & LRow).Value = LProject_Ref Then
LFound = True
Range("S" & LRow).Value = Lstair_lift_invoice_1
Range("T" & LRow).Value = Lstair_lift_invoice_2
Range("U" & LRow).Value = Lstair_lift_invoice_3
Range("V" & LRow).Value = Lstair_lift_invoice_4
Range("J" & LRow).Value = Lstair_lift_planned_date_1
Range("K" & LRow).Value = Lstair_lift_planned_date_2
Range("L" & LRow).Value = Lstair_lift_planned_date_3
Range("M" & LRow).Value = Lstair_lift_planned_date_4
Range("N" & LRow).Value = Lstair_lift_actual_date_1
Range("O" & LRow).Value = Lstair_lift_actual_date_2
Range("P" & LRow).Value = Lstair_lift_actual_date_3
Range("Q" & LRow).Value = Lstair_lift_actual_date_4
Range("I" & LRow).Value = Lstair_Additional_Information
Range("AG" & LRow).Value = Lelderly_alarm_invoice_1
Range("AH" & LRow).Value = Lelderly_alarm_invoice_2
Range("AI" & LRow).Value = Lelderly_alarm_invoice_3
Range("AJ" & LRow).Value = Lelderly_alarm_invoice_4
Range("AB" & LRow).Value = Leam_actual_date_1
Range("AC" & LRow).Value = Leam_actual_date_2
Range("AD" & LRow).Value = Leam_actual_date_3
Range("AE" & LRow).Value = Leam_actual_date_4
Range("X" & LRow).Value = Leam_planned1
Range("Y" & LRow).Value = Leam_planned2
Range("Z" & LRow).Value = Leam_planned3
Range("AA" & LRow).Value = Leam_planned4
Range("AK" & LRow).Value = Leam_notes
Range("CW" & LRow).Value = LFire_Alarms_1
Range("CX" & LRow).Value = LFire_Alarms_2
Range("CY" & LRow).Value = LFire_Alarms_3
Range("CZ" & LRow).Value = LFire_Alarms_4
Range("CR" & LRow).Value = LFire_Alarms_actual1
Range("CS" & LRow).Value = LFire_Alarms_actual2
Range("CT" & LRow).Value = LFire_Alarms_actual3
Range("CU" & LRow).Value = LFire_Alarms_actual4
Range("CN" & LRow).Value = LFire_Alarms_planned1
Range("CO" & LRow).Value = LFire_Alarms_planned2
Range("CP" & LRow).Value = LFire_Alarms_planned3
Range("CQ" & LRow).Value = LFire_Alarms_planned4
Range("CM" & LRow).Value = LFire_Alarms_Notes
Range("CH" & LRow).Value = LFire_equipment_1
Range("CI" & LRow).Value = LFire_equipment_2
Range("CJ" & LRow).Value = LFire_equipment_3
Range("CK" & LRow).Value = LFire_equipment_4
Range("CE" & LRow).Value = LFire_equip_planned1
Range("CF" & LRow).Value = LFire_equip_actual1
Range("CD" & LRow).Value = LFire_equip_notes
Range("EI" & LRow).Value = LEmerg_Lighting_1
Range("EJ" & LRow).Value = LEmerg_Lighting_2
Range("EK" & LRow).Value = LEmerg_Lighting_3
Range("EL" & LRow).Value = LEmerg_Lighting_4
Range("ED" & LRow).Value = LEmerg_lights_actual1
Range("EE" & LRow).Value = LEmerg_lights_actual2
Range("DZ" & LRow).Value = Lemerg_lights_plan1
Range("EA" & LRow).Value = Lemerg_lights_plan2
Range("DY" & LRow).Value = Lemerg_lights_notes
Range("BE" & LRow).Value = LPat_test_1
Range("BF" & LRow).Value = LPat_test_2
Range("BG" & LRow).Value = LPat_test_3
Range("BH" & LRow).Value = LPat_test_4
Range("BC" & LRow).Value = LPat_test_actual1
Range("BB" & LRow).Value = LPat_test_plan1
Range("BI" & LRow).Value = LPat_test_notes
Range("AW" & LRow).Value = Lbed_hoist_1
Range("AX" & LRow).Value = Lbed_hoist_2
Range("AY" & LRow).Value = Lbed_hoist_3
Range("AZ" & LRow).Value = Lbed_hoist_4
Range("AR" & LRow).Value = Lbhb_actual1
Range("AS" & LRow).Value = Lbhb_actual2
Range("AT" & LRow).Value = Lbhb_actual3
Range("AU" & LRow).Value = Lbhb_actual4
Range("AN" & LRow).Value = Lbhb_plan1
Range("AO" & LRow).Value = Lbhb_plan2
Range("AP" & LRow).Value = Lbhb_plan3
Range("AQ" & LRow).Value = Lbhb_plan4
Range("AM" & LRow).Value = Lbhb_notes
Range("BY" & LRow).Value = Lin_block_lift_1
Range("BZ" & LRow).Value = Lin_block_lift_2
Range("CA" & LRow).Value = Lin_block_lift_3
Range("CB" & LRow).Value = Lin_block_lift_4
Range("BR" & LRow).Value = Lin_block_actual1
Range("BS" & LRow).Value = Lin_block_actual2
Range("BT" & LRow).Value = Lin_block_actual3
Range("BU" & LRow).Value = Lin_block_actual4
Range("BV" & LRow).Value = Lin_block_actual5
Range("BW" & LRow).Value = Lin_block_actual6
Range("BL" & LRow).Value = Lin_block_plan1
Range("BM" & LRow).Value = Lin_block_plan2
Range("BN" & LRow).Value = Lin_block_plan3
Range("BO" & LRow).Value = Lin_block_plan4
Range("BP" & LRow).Value = Lin_block_plan5
Range("BQ" & LRow).Value = Lin_block_plan6
Range("BK" & LRow).Value = Lin_block_notes
Range("DI" & LRow).Value = LChim1
Range("DJ" & LRow).Value = LChim2
Range("DF" & LRow).Value = LChim_actual1
Range("DG" & LRow).Value = LChim_actual2
Range("DD" & LRow).Value = LChim_plan1
Range("DE" & LRow).Value = LChim_plan2
Range("DK" & LRow).Value = LChim_notes
Range("DS" & LRow).Value = LCOMBO1
Range("DT" & LRow).Value = LCOMBO2
Range("DP" & LRow).Value = LCOMBO_actual1
Range("DN" & LRow).Value = LCOMBO_plan1
Range("DW" & LRow).Value = LCOMBO_notes
Range("ET" & LRow).Value = LGecko1
Range("EU" & LRow).Value = LGecko2
Range("EQ" & LRow).Value = LGecko_actual1
Range("EO" & LRow).Value = LGecko_plan1
Range("EX" & LRow).Value = LGecko_notes
Range("FF" & LRow).Value = LNU
Range("FG" & LRow).Value = LNU2
Range("FC" & LRow).Value = LNu_actual1
Range("FA" & LRow).Value = LNu_plan1
Range("FJ" & LRow).Value = LNu_notes
Range("FV" & LRow).Value = LPump1
Range("FW" & LRow).Value = LPump2
Range("FX" & LRow).Value = LPump3
Range("FY" & LRow).Value = LPump4
Range("FQ" & LRow).Value = LPump_actual1
Range("FR" & LRow).Value = LPump_actual2
Range("FS" & LRow).Value = LPump_actual3
Range("FT" & LRow).Value = LPump_actual4
Range("FM" & LRow).Value = LPump_plan1
Range("FN" & LRow).Value = LPump_plan2
Range("FO" & LRow).Value = LPump_plan3
Range("FP" & LRow).Value = LPump_plan4
Range("FZ" & LRow).Value = LPump_Notes
Range("GL" & LRow).Value = LSmoke1
Range("GM" & LRow).Value = LSmoke2
Range("GN" & LRow).Value = LSmoke3
Range("GO" & LRow).Value = LSmoke4
Range("GG" & LRow).Value = LSmoke_actual1
Range("GH" & LRow).Value = LSmoke_actual2
Range("GI" & LRow).Value = LSmoke_actual3
Range("GJ" & LRow).Value = LSmoke_actual4
Range("GC" & LRow).Value = LSmoke_Plan1
Range("GD" & LRow).Value = LSmoke_Plan2
Range("GE" & LRow).Value = LSmoke_Plan3
Range("GF" & LRow).Value = LSmoke_Plan4
Range("GP" & LRow).Value = LSmoke_notes
'Encountered a blank Project_Ref number (assuming end of list on full_list)
ElseIf IsEmpty(Range("A" & LRow).Value) = True Then
MsgBox ("No Match was found.Changes were not made.")
Exit Sub
End If
LRow = LRow + 1
Loop
'Reposition back on Invoice Control
Sheets("Invoice Control").Select
Range("A10").Select
MsgBox ("Yay Changes Complete :)")
End SubThe combo box uses a Location Ref from the sheet 'Full List' as the info provided in the drop down, but this has now become to large to manage and I need a way of filtering down the data.I have a a number of fields that I can filter with, I just Have no Idea where to start with this one. My preference would be for either radio button filters or multiple Combo boxes. I need to keep the read and write to functions as is if possible. Any help greatfully recieved.
Thanks
Jaysteruk
