Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox list to leave ONLY related values in list ??
I currently have combobox1 populated with values from sheet1.Range(R2:R12)
Combobox2 is populated with ALL Numerical values in sheet1.Range(A:A). But i want ONLY Combobox2 populated with ALL Numerical values that have ..offset(-1,2).value = combobox1.value The code to popultate combobox2 currently is: Private Sub UserForm_Initialize() Application.ScreenUpdating = False With ActiveWorkbook.workSheets1 ..Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Need something like: Private Sub UserForm_Initialize() Application.ScreenUpdating = False If combobox2.value offset(-1, 2).value < combobox1.value then REMOVE from Combobox2.list ' <========= This bit here With ActiveWorkbook.workSheets1 ..Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Have been assisted with some other code to find the cell and it may be needed to fix the above: Dim rngFound As Range On Error Resume Next With workSheets2.Range("C:C") Set rngFound = .Find(what:=Me.ComboBox1.Value, after:=.Range("C1"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) ' Top Section Values Tough one for me to work out. Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox list to leave ONLY related values in list ??
Corey, instead of trying to remove them, just exclude them from being added
to the combobox with a 3rd IF statement: For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).value < combobox1.value Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If End If Next Mike F "Corey" wrote in message ... I currently have combobox1 populated with values from sheet1.Range(R2:R12) Combobox2 is populated with ALL Numerical values in sheet1.Range(A:A). But i want ONLY Combobox2 populated with ALL Numerical values that have .offset(-1,2).value = combobox1.value The code to popultate combobox2 currently is: Private Sub UserForm_Initialize() Application.ScreenUpdating = False With ActiveWorkbook.workSheets1 .Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Need something like: Private Sub UserForm_Initialize() Application.ScreenUpdating = False If combobox2.value offset(-1, 2).value < combobox1.value then REMOVE from Combobox2.list ' <========= This bit here With ActiveWorkbook.workSheets1 .Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Have been assisted with some other code to find the cell and it may be needed to fix the above: Dim rngFound As Range On Error Resume Next With workSheets2.Range("C:C") Set rngFound = .Find(what:=Me.ComboBox1.Value, after:=.Range("C1"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) ' Top Section Values Tough one for me to work out. Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox list to leave ONLY related values in list ??
Thanks Mike,
Got there in the end with a few added conditions and a bit of fiddling around. Corey.... "Mike Fogleman" wrote in message m... Corey, instead of trying to remove them, just exclude them from being added to the combobox with a 3rd IF statement: For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).value < combobox1.value Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If End If Next Mike F "Corey" wrote in message ... I currently have combobox1 populated with values from sheet1.Range(R2:R12) Combobox2 is populated with ALL Numerical values in sheet1.Range(A:A). But i want ONLY Combobox2 populated with ALL Numerical values that have .offset(-1,2).value = combobox1.value The code to popultate combobox2 currently is: Private Sub UserForm_Initialize() Application.ScreenUpdating = False With ActiveWorkbook.workSheets1 .Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Need something like: Private Sub UserForm_Initialize() Application.ScreenUpdating = False If combobox2.value offset(-1, 2).value < combobox1.value then REMOVE from Combobox2.list ' <========= This bit here With ActiveWorkbook.workSheets1 .Select For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then ComboBox2.AddItem Cells(myrow, 1) End If End If Next End With End Sub Have been assisted with some other code to find the cell and it may be needed to fix the above: Dim rngFound As Range On Error Resume Next With workSheets2.Range("C:C") Set rngFound = .Find(what:=Me.ComboBox1.Value, after:=.Range("C1"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False) ' Top Section Values Tough one for me to work out. Corey.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.AddItem list and populating combobox with created list | Excel Discussion (Misc queries) | |||
Using Unique Values in as a combobox value list | Excel Discussion (Misc queries) | |||
Combobox List to list Numerical Values ONLY in Column A is specific Sheet.... | Excel Programming | |||
How Do I Get A ComboBox To Add Entry To It's List If Not In List | Excel Programming | |||
how to populate a combobox with a list of unique values? | Excel Programming |