angelfromabove
asked on
Auto-Filter a list using a ComboBox with no Code
Hello-
Is there any way to add a "menu of options" at the top of a spreadsheet in a combobox without VBA Code? When an item in the drop-down is selected, it would also act as an auto-filter.
Thanks!
Is there any way to add a "menu of options" at the top of a spreadsheet in a combobox without VBA Code? When an item in the drop-down is selected, it would also act as an auto-filter.
Thanks!
ASKER
Hey Glenn!
It's been a while! My project has been put on hold with the Forecast Model, but I will be back to it very soon!
Ok, so in the interim, since I have to use code, I would need 2 combo boxes which would filter the spreadsheet upon selection of a value in the combo-boxes (similar to how auto-filter) works but the combo boxes look prettier and give the illusion of a visible menu:
1) !st Combo box would be using values in Column C
2) 2nd Combo box would be using values in Column I
Thanks!
It's been a while! My project has been put on hold with the Forecast Model, but I will be back to it very soon!
Ok, so in the interim, since I have to use code, I would need 2 combo boxes which would filter the spreadsheet upon selection of a value in the combo-boxes (similar to how auto-filter) works but the combo boxes look prettier and give the illusion of a visible menu:
1) !st Combo box would be using values in Column C
2) 2nd Combo box would be using values in Column I
Thanks!
Hello again! It has been a while.
Regarding this request:
1) Will there need to be an option to select more than one value in these combo boxes?
2) I'd the content in columns C & I distinct or are there multiple occurrences of values?
An example workbook will be helpful, if possible.
Regards,
-Glenn
Sent from my Windows Phone
Regarding this request:
1) Will there need to be an option to select more than one value in these combo boxes?
2) I'd the content in columns C & I distinct or are there multiple occurrences of values?
An example workbook will be helpful, if possible.
Regards,
-Glenn
Sent from my Windows Phone
ASKER
My responses are:
1) No, I only need one value at a time, but I guess I would need an "All" button when no values are selected.
2)There are multiple occurences of the same values in each column, but in the menu box, I would only need one occurence of each value (just like auto-filter)
3) In column C I have 2 unique values and in Column I, there are 10 unique values. (That could change, but I just wanted to give you an idea)
Thanks!
Oh, my apologies, I'm so swamped, I truly didn't have time to mock up the data for a sample workbook because because I'm on a time constraint and I thought this would be straight forward, but let me know if you absolutely need one.
Cheers!
1) No, I only need one value at a time, but I guess I would need an "All" button when no values are selected.
2)There are multiple occurences of the same values in each column, but in the menu box, I would only need one occurence of each value (just like auto-filter)
3) In column C I have 2 unique values and in Column I, there are 10 unique values. (That could change, but I just wanted to give you an idea)
Thanks!
Oh, my apologies, I'm so swamped, I truly didn't have time to mock up the data for a sample workbook because because I'm on a time constraint and I thought this would be straight forward, but let me know if you absolutely need one.
Cheers!
Here is a preliminary workbook for you to review. I haven't included the logic to auto-populate the data validation boxes on top, but will follow up with that after making any changes you need to this.
Again, this requires VBA. There is a Worksheet_Change event in the main sheet that checks to see if the values for either selection box have been changed and then applies filtering as necessary. The boxes can be moved anywhere, but obviously should stay above the data table.
Regards,
-Glenn
EE-ComboFilter.xlsm
Again, this requires VBA. There is a Worksheet_Change event in the main sheet that checks to see if the values for either selection box have been changed and then applies filtering as necessary. The boxes can be moved anywhere, but obviously should stay above the data table.
Regards,
-Glenn
EE-ComboFilter.xlsm
I've added new code that will automatically update the data validation lists for the two filter boxes and am attaching a revised version of the workbook.
-Glenn
PS Thanks for dlmille for his QSort subroutine allowing the sorting of values in an array. I used that here.
EE-ComboFilter.xlsm
-Glenn
PS Thanks for dlmille for his QSort subroutine allowing the sorting of values in an array. I used that here.
EE-ComboFilter.xlsm
ASKER
Perfect, this is exactly what I need! What rows of code do I need to amend when I place this in my code? When I get that answer, I will give you your points and be on my way.:-)
Thanks again!
Thanks again!
Have you looked at the Slicer Option? Not as aesthetically pleasing as a Combo Box but does the job without the requirement for VBA (as requested).
Thanks
Rob H
Thanks
Rob H
ASKER
Thanks Rob-
Great suggestion, but I have a specific request for the aesthetically pleasing Combo Box. Plus, I thought the slicer only works with Pivot Tables or Power Pivot and I didn't want to go that route. Glenn's solution works for me.
Great suggestion, but I have a specific request for the aesthetically pleasing Combo Box. Plus, I thought the slicer only works with Pivot Tables or Power Pivot and I didn't want to go that route. Glenn's solution works for me.
A -
I've annotated the example workbook with comments that highlight all the variable names that you need to change to use this code in your own workbook. All this code should be added to the same sheet object that the table is in. Do a search (in VBA) for "CHANGE" and you will see each line.
Also, I used an Excel Table in this example file (name: "Table1") so I wouldn't have to enter a range. If your data is not in an Excel table, but at least has a definitive width in columns you can replace "Table1" with something like "$A$6:$I$10000" (using some large number for maximum rows) and that will work.
-Glenn
EE-ComboFilter-b.xlsm
I've annotated the example workbook with comments that highlight all the variable names that you need to change to use this code in your own workbook. All this code should be added to the same sheet object that the table is in. Do a search (in VBA) for "CHANGE" and you will see each line.
Also, I used an Excel Table in this example file (name: "Table1") so I wouldn't have to enter a range. If your data is not in an Excel table, but at least has a definitive width in columns you can replace "Table1" with something like "$A$6:$I$10000" (using some large number for maximum rows) and that will work.
-Glenn
EE-ComboFilter-b.xlsm
Rob has a good point: If you have Excel 2013, Slicers are available for Excel Tables.
It took me a while to use Slicers with PivotTables, but I'm warming up to them now, even redesigning some of my existing PTs at work to use them. I do like seeing value options all the time, especially for PT filter values.
It took me a while to use Slicers with PivotTables, but I'm warming up to them now, even redesigning some of my existing PTs at work to use them. I do like seeing value options all the time, especially for PT filter values.
ASKER
Thanks Glenn -
I am familiar with them and use them regularly for Pivot tables. They are wonderful! This is for a different purpose though, and the users don't want this in a pivot table format unfortunately. Thanks for the file with the commentary, I will amend my file and get back to you soon.
I am familiar with them and use them regularly for Pivot tables. They are wonderful! This is for a different purpose though, and the users don't want this in a pivot table format unfortunately. Thanks for the file with the commentary, I will amend my file and get back to you soon.
ASKER
Glenn -
I followed the instructions, pasted the code in, changed the items that needed changing. Then I went back to the Worksheet and added my own Combo Box, and linked it to Cell C1 and it's not displaying any values??
I followed the instructions, pasted the code in, changed the items that needed changing. Then I went back to the Worksheet and added my own Combo Box, and linked it to Cell C1 and it's not displaying any values??
I apologize; there are two lines in the Worksheet_Change event I forgot to highlight:
Change Lines 8 and 16. Hopefully that should fix it.
-Glenn
Private Sub Worksheet_Change(ByVal Target As Range)
'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
If Target.Address = "$C$2" Then 'CHANGE: $C$2 to actual location of the droplist cell that will filter column C
If Target.Value = "All" Then
Range("A6").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
Else
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=Range("C2").Value 'CHANGE C2 to actual location of droplist cell that will filter column C
End If
Range("C2").Select
ElseIf Target.Address = "$I$2" Then 'CHANGE: $I$2 to actual location of the droplist cell that will filter column I
If Target.Value = "All" Then
Range("A6").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9
Else
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:=Range("I2").Value 'CHANGE I2 to actual location of droplist cell that will filter column I
End If
Range("I2").Select
End If
End Sub
Change Lines 8 and 16. Hopefully that should fix it.
-Glenn
ASKER
Nope, I already found those lines and changed everything. Do I need to add my own Combo Boxes, or does the macro do it?
ComboBoxes: You shouldn't have to do anything; the code assumes two locations for the filtering of columns C and I. My example used C2 and I2; you can have any two you want, but they should be on the same sheet as the data (above the data is best) and have to be consistently referenced in the code.
ASKER
Yes, I did that, I used C1 and I1, above the data. Do I have to insert the boxes via Developer and an Active X control is what I'm asking?
ASKER
Here's what I amended the code to: Maybe you will see something I'm not seeing.
Private Sub Worksheet_Activate()
'Re-builds the two data-validation filters based on the data in columns C & I
Dim Dic As Object
Dim rng As Variant
Dim x As Long
Dim arrUList As Variant
Dim strDVList As String
'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r
'Column C
rng = Range("C3", Range("C3").End(xlDown)) 'CHANGE: C7 to first row of data in column C
Set Dic = CreateObject("Scripting.Di ctionary")
Dic.compareMode = vbTextCompare
For x = 1 To UBound(rng, 1)
Dic.Item(rng(x, 1)) = rng(x, 1)
Next x
'assign Dic to array then insert in cells
arrUList = Dic.items
Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
For x = 0 To UBound(arrUList)
strDVList = strDVList & "," & arrUList(x)
Next x
strDVList = "All" & strDVList
Range("C1").Select 'CHANGE: C2 to actual location of the droplist cell that will filter column C
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt op, Operator:= _
xlBetween, Formula1:=strDVList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Column I
strDVList = ""
arrUList = ""
rng = Range("I3", Range("I3").End(xlDown)) 'CHANGE: I7 to first row of data in column I
Set Dic = CreateObject("Scripting.Di ctionary")
Dic.compareMode = vbTextCompare
For x = 1 To UBound(rng, 1)
Dic.Item(rng(x, 1)) = rng(x, 1)
Next x
'assign Dic to array then insert in cells
arrUList = Dic.items
Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
For x = 0 To UBound(arrUList)
strDVList = strDVList & "," & arrUList(x)
Next x
strDVList = "All" & strDVList
Range("I1").Select 'CHANGE: I2 to actual location of the droplist cell that will filter column I
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt op, Operator:= _
xlBetween, Formula1:=strDVList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
If Target.Address = "$C$1" Then 'CHANGE: $C$2 to actual location of the droplist cell that will filter column C
If Target.Value = "All" Then
Range("A2").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r Field:=3
Else
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r Field:=3, Criteria1:=Range("C1").Val ue
End If
Range("C1").Select
ElseIf Target.Address = "$I$1" Then 'CHANGE: $I$2 to actual location of the droplist cell that will filter column I
If Target.Value = "All" Then
Range("A2").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r Field:=9
Else
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r Field:=9, Criteria1:=Range("I1").Val ue
End If
Range("I1").Select
End If
End Sub
Sub QSort(sortArray As Variant, ByVal leftIndex As Integer, ByVal rightIndex As Integer)
'Code by EE expert, dlmille
'See https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_5062-Drop-Down-List-with-Unique-Distinct-Values-enhancing-the-ComboBox-with-a-few-steps-and-a-little-code.html
Dim compValue As Variant
Dim i As Integer
Dim J As Integer
Dim tempVar As Variant
i = leftIndex
J = rightIndex
compValue = sortArray(Int((i + J) / 2))
Do
Do While (sortArray(i) < compValue And i < rightIndex)
i = i + 1
Loop
Do While (compValue < sortArray(J) And J > leftIndex)
J = J - 1
Loop
If i <= J Then
tempVar = sortArray(i)
sortArray(i) = sortArray(J)
sortArray(J) = tempVar
i = i + 1
J = J - 1
End If
Loop While i <= J
If leftIndex < J Then QSort sortArray, leftIndex, J
If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub
Private Sub Worksheet_Activate()
'Re-builds the two data-validation filters based on the data in columns C & I
Dim Dic As Object
Dim rng As Variant
Dim x As Long
Dim arrUList As Variant
Dim strDVList As String
'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
ActiveSheet.ListObjects("$
'Column C
rng = Range("C3", Range("C3").End(xlDown)) 'CHANGE: C7 to first row of data in column C
Set Dic = CreateObject("Scripting.Di
Dic.compareMode = vbTextCompare
For x = 1 To UBound(rng, 1)
Dic.Item(rng(x, 1)) = rng(x, 1)
Next x
'assign Dic to array then insert in cells
arrUList = Dic.items
Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
For x = 0 To UBound(arrUList)
strDVList = strDVList & "," & arrUList(x)
Next x
strDVList = "All" & strDVList
Range("C1").Select 'CHANGE: C2 to actual location of the droplist cell that will filter column C
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=strDVList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Column I
strDVList = ""
arrUList = ""
rng = Range("I3", Range("I3").End(xlDown)) 'CHANGE: I7 to first row of data in column I
Set Dic = CreateObject("Scripting.Di
Dic.compareMode = vbTextCompare
For x = 1 To UBound(rng, 1)
Dic.Item(rng(x, 1)) = rng(x, 1)
Next x
'assign Dic to array then insert in cells
arrUList = Dic.items
Call QSort(arrUList, LBound(arrUList), UBound(arrUList))
For x = 0 To UBound(arrUList)
strDVList = strDVList & "," & arrUList(x)
Next x
strDVList = "All" & strDVList
Range("I1").Select 'CHANGE: I2 to actual location of the droplist cell that will filter column I
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=strDVList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'CHANGE "Table1" to the name of the Excel Table that contains all the data to be filtered/displayed
If Target.Address = "$C$1" Then 'CHANGE: $C$2 to actual location of the droplist cell that will filter column C
If Target.Value = "All" Then
Range("A2").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("$
Else
ActiveSheet.ListObjects("$
End If
Range("C1").Select
ElseIf Target.Address = "$I$1" Then 'CHANGE: $I$2 to actual location of the droplist cell that will filter column I
If Target.Value = "All" Then
Range("A2").Select 'CHANGE A6 to top-left cell in table
ActiveSheet.ListObjects("$
Else
ActiveSheet.ListObjects("$
End If
Range("I1").Select
End If
End Sub
Sub QSort(sortArray As Variant, ByVal leftIndex As Integer, ByVal rightIndex As Integer)
'Code by EE expert, dlmille
'See https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_5062-Drop-Down-List-with-Unique-Distinct-Values-enhancing-the-ComboBox-with-a-few-steps-and-a-little-code.html
Dim compValue As Variant
Dim i As Integer
Dim J As Integer
Dim tempVar As Variant
i = leftIndex
J = rightIndex
compValue = sortArray(Int((i + J) / 2))
Do
Do While (sortArray(i) < compValue And i < rightIndex)
i = i + 1
Loop
Do While (compValue < sortArray(J) And J > leftIndex)
J = J - 1
Loop
If i <= J Then
tempVar = sortArray(i)
sortArray(i) = sortArray(J)
sortArray(J) = tempVar
i = i + 1
J = J - 1
End If
Loop While i <= J
If leftIndex < J Then QSort sortArray, leftIndex, J
If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub
No; my example uses no ActiveX or form controls. It's all done in-sheet.
Okay, your controls are in C1 and I1. Can you specify the data range? Also, note that I used an Excel Table ("Table1"); if you're not using an Excel Table, that name will have to be replaced in the subroutines.
-Glenn
Okay, your controls are in C1 and I1. Can you specify the data range? Also, note that I used an Excel Table ("Table1"); if you're not using an Excel Table, that name will have to be replaced in the subroutines.
-Glenn
Okay, this line in the Worksheet_Activate code has to be changed from:
ActiveSheet.ListObjects("$ A$2:$o$100 00").Range .AutoFilte r
to
ActiveSheet.Range("$A$2:$O $10000").A utoFilter
and all similar lines that originally referenced the Excel Table object. Sorry for the oversight.
I've updated your code and put it on the Sheet2 object in the attached workbook. Sheet2 should mimic your layout so you can test. If it looks good, just copy ALL the code from the Sheet2 object and replace yours.
-Glenn
EE-ComboFilter-b.xlsm
ActiveSheet.ListObjects("$
to
ActiveSheet.Range("$A$2:$O
and all similar lines that originally referenced the Excel Table object. Sorry for the oversight.
I've updated your code and put it on the Sheet2 object in the attached workbook. Sheet2 should mimic your layout so you can test. If it looks good, just copy ALL the code from the Sheet2 object and replace yours.
-Glenn
EE-ComboFilter-b.xlsm
ASKER
Ahhh nothing after I pasted Sheet 2 in my workbook. I checked again and my data is formatted as a table (not a pivot table), Would that make a difference? I'm not getting any error messages, just no boxes are appearing. :-(
Dangit. :-)
If your data is an Excel Table, then it has a name assigned (check Menu: Formulas, Name Manager - see if there is a "Tablex" name listed). If it does, then my original code would be the way to go (see the code in Sheet1).
If you click on Cells C1 or I1, do you not see a data validation drop arrow appear? That's what should happen, but only if you select the cell.
-Glenn
If your data is an Excel Table, then it has a name assigned (check Menu: Formulas, Name Manager - see if there is a "Tablex" name listed). If it does, then my original code would be the way to go (see the code in Sheet1).
If you click on Cells C1 or I1, do you not see a data validation drop arrow appear? That's what should happen, but only if you select the cell.
-Glenn
ASKER
Very sorry, It says it's called Table1 and here's how it's defined. =Sheet1!$A$1:$P$1005. I do not see a data validation drop-down arrow. The cell is blank.
Would you mind changing the code once more and attaching a new worksheet if it was the Table1 name causing me to be off?
My apologies again.
Would you mind changing the code once more and attaching a new worksheet if it was the Table1 name causing me to be off?
My apologies again.
There is problem here. You have the table defined starting in row 1 ($A$1...) and the two filtering cells are also in row 1 (C1 and I1). You can't do this; it affects the header row which is required for an Excel Table. This may also be why filtering isn't happening.
I've re-defined the example data on Sheet2 as an Excel Table - named "Table2" that covers $A$3:$P$1005 (actually A2, but header row is defined). And I updated the Worksheet_Change and Worksheet_Activate codes accordingly.
-Glenn
EE-ComboFilter-c.xlsm
I've re-defined the example data on Sheet2 as an Excel Table - named "Table2" that covers $A$3:$P$1005 (actually A2, but header row is defined). And I updated the Worksheet_Change and Worksheet_Activate codes accordingly.
-Glenn
EE-ComboFilter-c.xlsm
And, I have to ask a possibly overly-simplistic question, but are you Enabling content? That is, activating macros?
-Glenn
-Glenn
ASKER
1) Of course Enabling Content.
2) My headers are actually in A2, so is that why you are starting it in A3?
2) My headers are actually in A2, so is that why you are starting it in A3?
1) okay. whew.
2) If you look at the range defined for "Table2" is literally shows $A$3:$P$1005, even though my headers are also in row 2. That's just how Excel sets up the range. So, no difference here.
So, my workbook behaves as you think it should? That is, when you view "Sheet2" you see a table with two colored cells on top and if you click on either cell, a drop list appears letting you select any of the values in that column?
I really don't know where else to go with this short of seeing the actual workbook. Sorry.
-Glenn
2) If you look at the range defined for "Table2" is literally shows $A$3:$P$1005, even though my headers are also in row 2. That's just how Excel sets up the range. So, no difference here.
So, my workbook behaves as you think it should? That is, when you view "Sheet2" you see a table with two colored cells on top and if you click on either cell, a drop list appears letting you select any of the values in that column?
I really don't know where else to go with this short of seeing the actual workbook. Sorry.
-Glenn
ASKER
Yes, your table performs perfectly as expected. I changed the code to Table1 in my file and my file looks exactly like yours. I have no idea, but still nothing, uggh.?
Is there something that triggers the macro, like hitting enter or updating a cell or something, or does it just run open opening the workbook? I don't see anything else wrong unless I don't have some of the code that you used in my library, but that would give me an error, and it didn't.
Is there something that triggers the macro, like hitting enter or updating a cell or something, or does it just run open opening the workbook? I don't see anything else wrong unless I don't have some of the code that you used in my library, but that would give me an error, and it didn't.
ASKER
GOOD NEWS!, it seems to be working now, I PRAYED, closed it, saved it and reopened it and took it off of the Sharepoint Site. The bad news is that it's not showing all of the values in the Row. For example, I have the values of "S" and "N" in Column C. The drop-down is only showing "S" and "All". The "S" values stop at row 25 and the "N" values begin on Row 26. It seems like it's not going down the whole column or something???
ASKER
My drop-down data-validation list appears to be truncated. It's not picking up all of the values in the column, nor picking up new values when a value is changed. I looked at the Data Validation settings on the menu, and it didn't show all of the values. I manually added them to my Data Validation drop-down list and it worked, but I shouldn't have to do that right?
ASKER
ok, I just checked your spreadsheet and it doesn't auto udpate values either. I have to go into the VBA code and click Run Code in order for the values to update in the Data Validation. Can we fix that, so users don't have to hit run to trigger the macro?
I really don't know what to say. If my example spreadsheet does not work for you, then there is some environment difference on your machine.
Going back to your post (40374244): the two macros are triggered by specific actions in Excel:
Worksheet_Activate - happens whenever the sheet is selected to view.
Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.
I would have thought that perhaps you had added the Worksheet_Change / Activate events (code) to a module or another sheet by accident, but since you state that the example workbook does not run automatically, I'm at a loss as to how to assist.
Sorry,
-Glenn
Going back to your post (40374244): the two macros are triggered by specific actions in Excel:
Worksheet_Activate - happens whenever the sheet is selected to view.
Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.
I would have thought that perhaps you had added the Worksheet_Change / Activate events (code) to a module or another sheet by accident, but since you state that the example workbook does not run automatically, I'm at a loss as to how to assist.
Sorry,
-Glenn
ASKER
I didn't say it didn't work, we are almost there. I was just saying it doesn't work instantly and was trying to work with you to troubleshoot the reasons why. Can you please explain further what you mean by your statement below?
"Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.
I guess I'm not sure what you mean by it is bypassed?
And please don't take my questions personally, I'm just trying to get it to work as expected and I'm being transparent as to what I'm seeing. I changed the cell values in two of the rows, and I had to click away from the spreadsheet and go back to it in order for the macro to run, I am just wondering if the macro can be further tweaked to be triggered by anytime someone hits enter after they change any cell, so it's more automatic. If you can't amend it that way, I will have to give you the points for what you did and open it up again. Because if new values are not automatically showing up in the Data Validation list, it is not working for me.
Thanks again.
"Worksheet_Change - happens whenever any cell value is changed in the sheet.
It is bypassed when any cell other than the two filtering cells (C1 and I1 in your case) are changed.
I guess I'm not sure what you mean by it is bypassed?
And please don't take my questions personally, I'm just trying to get it to work as expected and I'm being transparent as to what I'm seeing. I changed the cell values in two of the rows, and I had to click away from the spreadsheet and go back to it in order for the macro to run, I am just wondering if the macro can be further tweaked to be triggered by anytime someone hits enter after they change any cell, so it's more automatic. If you can't amend it that way, I will have to give you the points for what you did and open it up again. Because if new values are not automatically showing up in the Data Validation list, it is not working for me.
Thanks again.
Worksheet_Change: I coded that so it would only run if there was a change to the values in cells C1 or I1. This limits it to only changing the filters on the table when either of those cells are changed. If any other cell is changed, nothing happens.
-------------------------- ---------- ---------- --------
You are asking for functionality beyond the scope of the original question.
As I pointed out, that was not possible without VBA Code. Even a ComboBox form control with the unique data values for a given column inserted (say, via an array function on a separate sheet) will not act as an AutoFilter control on a data table.
However, I was able to show by example how one could create an external cell that would 1) have a data validation list of the unique values of a given column and 2) control the filtering of that column using VBA (with a Worksheet_Change event).
It now sounds like you want the filters to be activated/refreshed whenever new data is added to the table. That is possible, but problematic. If data is added that is not within the filter specifications and an automatic update (like Worksheet_Change) is in effect, then that data row would disappear (i.e., be hidden) immediately upon entry - likely halting further entry. This would happen if a new value was added to column C or I that didn't already exist and would not update the data validation list either.
The only way that I can think of to make this happen is to include new features (form buttons) that would either "Refresh" or "Reset" the filtering on the data. This would allow the user to remain on the sheet, change/add/remove data as-needed and then re-apply the visible filters again. Or, just turn off all filters at once (in essence, setting both filters to "All").
If you think my example file and code does not meet the original request, I understand and will defer to other experts to provide a workable solution for you. But, if it does satisfy the scope of your original question and you would like to enhance that, please open a new question with the new behavior you want to see. Feel free to use my example (or your modified copy) as a basis; that will be very helpful to EE experts.
-Glenn
--------------------------
You are asking for functionality beyond the scope of the original question.
Is there any way to add a "menu of options" at the top of a spreadsheet in a combobox without VBA Code? When an item in the drop-down is selected, it would also act as an auto-filter.
As I pointed out, that was not possible without VBA Code. Even a ComboBox form control with the unique data values for a given column inserted (say, via an array function on a separate sheet) will not act as an AutoFilter control on a data table.
However, I was able to show by example how one could create an external cell that would 1) have a data validation list of the unique values of a given column and 2) control the filtering of that column using VBA (with a Worksheet_Change event).
It now sounds like you want the filters to be activated/refreshed whenever new data is added to the table. That is possible, but problematic. If data is added that is not within the filter specifications and an automatic update (like Worksheet_Change) is in effect, then that data row would disappear (i.e., be hidden) immediately upon entry - likely halting further entry. This would happen if a new value was added to column C or I that didn't already exist and would not update the data validation list either.
The only way that I can think of to make this happen is to include new features (form buttons) that would either "Refresh" or "Reset" the filtering on the data. This would allow the user to remain on the sheet, change/add/remove data as-needed and then re-apply the visible filters again. Or, just turn off all filters at once (in essence, setting both filters to "All").
If you think my example file and code does not meet the original request, I understand and will defer to other experts to provide a workable solution for you. But, if it does satisfy the scope of your original question and you would like to enhance that, please open a new question with the new behavior you want to see. Feel free to use my example (or your modified copy) as a basis; that will be very helpful to EE experts.
-Glenn
ASKER
Thank you.
I apologize for the misunderstanding and if you didn't have clarity on my original ask, but I am absolutely not asking for functionality beyond the original scope of the question. Part of my explanation states clearly that I wanted the combo box to act similarly to an auto-filter. That seems pretty straight forward to me, I am not enhancing anything, just trying to help you to understand what the ask is in subsequent posts. Obviously, when values change in the Columns that I wanted filtered (C and I) the, combo box or whatever you added should accommodate those values. That's how auto-filter works. There would be no other reasons the values in C1 or I1 would change unless they changed in the underlying spreadsheet, so I'm having trouble understanding what you mean when you say you coded it to change only when those values change in C1 or I1?
Anyway, I had a similar issue which was done for me a while back by one of the experts for a different workbook, so here's the Solution number:
Filter a worksheet using a combo or list box without VBA Code?
Zack Barresse2013-09-21 at 19:29:24ID: 39512358
I've also pasted the code below: Would it help you to look at this or should I just engage another Expert?
Please advise,
Thank you,
Option Explicit
'DATABANK SHEET
Private Const m_NEW As String = "A2:q"
'REPORT destination
Private Const m_Dest_All As String = "6:193"
Private Const m_Dest_NEW As String = "A7:l16"
Private Const m_Dest_RELO As String = "A19:l38"
Private Const m_Dest_RENO As String = "A41:l65"
Private Const m_Dest_REFRESH As String = "A68:l117"
Private Const m_Dest_CLOSURE As String = "A120:l188"
Private Const m_Dest_CONSOLIDATION As String = "A192:l193"
Private Abort As Boolean
Private Sub ComboBox1_Change()
Dim wsTemp As Worksheet
Dim wsData As Worksheet
Dim iLastRow As Long
Dim iLastData As Long
Static Abort1 As Boolean
If Abort Then GoTo ExitCombo1
If Abort1 Then GoTo ExitCombo1
Abort1 = True
Abort = Abort1
'/// Set variables
Set wsData = ThisWorkbook.Worksheets("D ATABANK")
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// NEW
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "NEW" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=NEW" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_NEW).Clear Contents
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_NEW, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// RELO
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REL" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=RELO" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_RELO).Clea rContents
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_RELO , ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// RENO
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REN" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=RENO" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_RENO).Clea rContents
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_RENO , ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// REFRESH
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REF" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=REFRESH" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_REFRESH).C learConten ts
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_REFR ESH, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// CLOSURE
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "CLO" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=CLOSURE" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_CLOSURE).C learConten ts
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_CLOS URE, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// CONSOLIDATION
'///////////////////////// ////////// ////////// ////////// ////////// ////////// /////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Then
wsData.Range("Headers").Of fset(1, 0).Resize(wsData.Rows.Coun t - 10, 1).Clear
wsData.Range("Criteria_Typ e").Formul a = "=" & Chr(34) & "=CONSOLIDATION" & Chr(34)
iLastData = wsData.Cells(wsData.Rows.C ount, "B").End(xlUp).Row
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang e("Criteri a"), CopyToRange:=wsData.Range( "Headers") , Unique:=False
iLastRow = wsData.Cells(wsData.Rows.C ount, wsData.Range("Headers").Co lumn).End( xlUp).Row
'/// Clear REPORT sheet
Me.Range(m_Dest_CONSOLIDAT ION).Clear Contents
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_CONS OLIDATION, ":")(0)).Resize(iLastRow - 1, 12).Value = wsData.Range("Headers")(1, 1).Offset(1, 0).Resize(iLastRow, 12).Value
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\\\\\\ \\\\\
Abort1 = False
Abort = False
Me.Activate
Me.Range("A1").Activate
ExitCombo1:
Application.EnableEvents = True
End Sub
Private Sub ComboBox2_Click()
Static UpdatedValue As String
Static Abort2 As Boolean
Abort = True
If Abort2 Then GoTo ExitCombo2
Abort2 = True
Application.EnableEvents = False
On Error GoTo ExitCombo2
Me.Range(m_Dest_All).RowHe ight = 0
Select Case Me.ComboBox2.Value
Case "ALL"
Me.Cells.EntireRow.AutoFit
Case "NEW"
Me.Range(m_Dest_NEW)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ NEW).Rows. Count + 2, Me.Range(m_Dest_NEW).Colum ns.Count). EntireRow. AutoFit
Case "REL"
Me.Range(m_Dest_RELO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ RELO).Rows .Count + 2, Me.Range(m_Dest_RELO).Colu mns.Count) .EntireRow .AutoFit
Case "REN"
Me.Range(m_Dest_RENO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ RENO).Rows .Count + 2, Me.Range(m_Dest_RENO).Colu mns.Count) .EntireRow .AutoFit
Case "REF"
Me.Range(m_Dest_REFRESH)(1 , 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ REFRESH).R ows.Count + 2, Me.Range(m_Dest_REFRESH).C olumns.Cou nt).Entire Row.AutoFi t
Case "CLO"
Me.Range(m_Dest_CLOSURE)(1 , 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ CLOSURE).R ows.Count + 2, Me.Range(m_Dest_CLOSURE).C olumns.Cou nt).Entire Row.AutoFi t
' Case "CON"
' Me.Range(m_Dest_CONSOLIDAT ION)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_ CONSOLIDAT ION).Rows. Count + 2, Me.Range(m_Dest_CONSOLIDAT ION).Colum ns.Count). EntireRow. AutoFit
End Select
Abort2 = False
Abort = False
Range("A1").Select
ExitCombo2:
Application.EnableEvents = True
End Sub
I apologize for the misunderstanding and if you didn't have clarity on my original ask, but I am absolutely not asking for functionality beyond the original scope of the question. Part of my explanation states clearly that I wanted the combo box to act similarly to an auto-filter. That seems pretty straight forward to me, I am not enhancing anything, just trying to help you to understand what the ask is in subsequent posts. Obviously, when values change in the Columns that I wanted filtered (C and I) the, combo box or whatever you added should accommodate those values. That's how auto-filter works. There would be no other reasons the values in C1 or I1 would change unless they changed in the underlying spreadsheet, so I'm having trouble understanding what you mean when you say you coded it to change only when those values change in C1 or I1?
Anyway, I had a similar issue which was done for me a while back by one of the experts for a different workbook, so here's the Solution number:
Filter a worksheet using a combo or list box without VBA Code?
Zack Barresse2013-09-21 at 19:29:24ID: 39512358
I've also pasted the code below: Would it help you to look at this or should I just engage another Expert?
Please advise,
Thank you,
Option Explicit
'DATABANK SHEET
Private Const m_NEW As String = "A2:q"
'REPORT destination
Private Const m_Dest_All As String = "6:193"
Private Const m_Dest_NEW As String = "A7:l16"
Private Const m_Dest_RELO As String = "A19:l38"
Private Const m_Dest_RENO As String = "A41:l65"
Private Const m_Dest_REFRESH As String = "A68:l117"
Private Const m_Dest_CLOSURE As String = "A120:l188"
Private Const m_Dest_CONSOLIDATION As String = "A192:l193"
Private Abort As Boolean
Private Sub ComboBox1_Change()
Dim wsTemp As Worksheet
Dim wsData As Worksheet
Dim iLastRow As Long
Dim iLastData As Long
Static Abort1 As Boolean
If Abort Then GoTo ExitCombo1
If Abort1 Then GoTo ExitCombo1
Abort1 = True
Abort = Abort1
'/// Set variables
Set wsData = ThisWorkbook.Worksheets("D
'/////////////////////////
'/// NEW
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "NEW" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_NEW).Clear
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_NEW,
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
'/////////////////////////
'/// RELO
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REL" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_RELO).Clea
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_RELO
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
'/////////////////////////
'/// RENO
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REN" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_RENO).Clea
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_RENO
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
'/////////////////////////
'/// REFRESH
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "REF" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_REFRESH).C
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_REFR
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
'/////////////////////////
'/// CLOSURE
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Or Me.ComboBox2.Value = "CLO" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_CLOSURE).C
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_CLOS
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
'/////////////////////////
'/// CONSOLIDATION
'/////////////////////////
'/// Get data
' If Me.ComboBox2.Value = "ALL" Then
wsData.Range("Headers").Of
wsData.Range("Criteria_Typ
iLastData = wsData.Cells(wsData.Rows.C
wsData.Range(m_NEW & iLastData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsData.Rang
iLastRow = wsData.Cells(wsData.Rows.C
'/// Clear REPORT sheet
Me.Range(m_Dest_CONSOLIDAT
'/// Ensure data was returned
If iLastRow > 2 Then
'/// Place new data
Me.Range(Split(m_Dest_CONS
End If
' End If
'\\\\\\\\\\\\\\\\\\\\\\\\\
Abort1 = False
Abort = False
Me.Activate
Me.Range("A1").Activate
ExitCombo1:
Application.EnableEvents = True
End Sub
Private Sub ComboBox2_Click()
Static UpdatedValue As String
Static Abort2 As Boolean
Abort = True
If Abort2 Then GoTo ExitCombo2
Abort2 = True
Application.EnableEvents = False
On Error GoTo ExitCombo2
Me.Range(m_Dest_All).RowHe
Select Case Me.ComboBox2.Value
Case "ALL"
Me.Cells.EntireRow.AutoFit
Case "NEW"
Me.Range(m_Dest_NEW)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_
Case "REL"
Me.Range(m_Dest_RELO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_
Case "REN"
Me.Range(m_Dest_RENO)(1, 1).Offset(-1, 0).Resize(Me.Range(m_Dest_
Case "REF"
Me.Range(m_Dest_REFRESH)(1
Case "CLO"
Me.Range(m_Dest_CLOSURE)(1
' Case "CON"
' Me.Range(m_Dest_CONSOLIDAT
End Select
Abort2 = False
Abort = False
Range("A1").Select
ExitCombo2:
Application.EnableEvents = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Glenn,
Thanks for all of your help! I will contact Zack at your suggestion.
Thanks for all of your help! I will contact Zack at your suggestion.
However, it's not possible to externally filter a set of data in this manner without VBA.
-Glenn