Thank you, Fluff13. This is exactly what I needed. Incorporating the worksheet object into the filter function allowed me to eliminate ws.Activate. The filter performs accurately this way and my screen flicker is gone. Many thanks!
Hello. I use the Filter Function below to filter a column in a Table and then populate a List Box with the results. I have found that I must activate the worksheet with the table in order for the function to filter correctly. But activating the worksheet causes the screen to flicker. Is there a way to avoid activating the worksheet yet have the Filter Function still filter correctly? Alternatively, can I avoid the screen flicker caused by activating the worksheet?Code
- Dim v, ws As Worksheet
- Dim vRows As Variant
- Set ws = ThisWorkbook.Worksheets("Sheet1")
- With ws.ListObjects("Table1").DataBodyRange
- vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
- v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)
Thanks for any assistance!
That's perfect!! All elements function properly. You've been a huge help in advancing my efforts. Many thanks!
Boom. That's it! Only thing I noticed is that (other than if the first or top item is selected), the output to the range is one-off from the item selected.
I simply can't get that to work. I have to be missing something. Can you put that in the example file and show me that section of the code? I fear I'm over complicating things.
Okay, but when looping through, how do I identify the selected item of the combo box list?
Maybe its because the combo box content I want is in the initialize sub and we're in the comb box change-event sub?
Those are the list box items (from array v). What I need to appear there is a single row containing the column items from the combo box (array g) for just the one selected item. That combo box array is populated when the form is initiated and captures 8 columns of data. I need those 8 columns to appear on the worksheet for the combo box item that is selected.
Much cleaner. Works slick. But its still bringing all the combo box items vs. just the selected item.
Missed your last piece before I sent...will try that....
That last bit did the trick! Thanks much for that!
As for line 125, I'm trying to paste ONLY the array contents for the combo box "selected" item into the worksheet, rather than the contents for all the items in the combo box. Its the If/Then statement that's the problem, as I can't figure out how to isolate just the selected item. ListIndex doesn't work, Value doesn't work, Selected doesn't work....using the variable q doesn't work. I must be missing something....
Thanks for your assist on this Fluff13....your time and effort is much appreciated. Did that piece of code work for you in the example file? I swapped it in and it pushed the type mismatch error down to the next line (v = Application.Index...) and the variable q results to "0" rather than the combo box selection.
As in the original code at the top of this post, if I use a specific text string to search for (such as "Group 1"), the combo box works like a charm and the associated items are brought into the list box. So I know its close. Just can't seem to solve for use with a variable.
Line 125 is also a problem. Not sure how to represent the "selected item" of the combo box in the If/Then statement, as .Selected(row) doesn't work the same as for a listbox.
Thank you, Fluff13. I've attached a sample file. Code errors occur on lines 25 and 125 of my combo box change event sub.
The goal is to populate the list box with specific records from the table based on the combo box selection AND move the contents of the combo box array to a designated range on the worksheet.
Some of my challenge is I'm finding combo boxes have different properties than list boxes in some cases and I'm not finding an intuitive work around.
Thanks for any help you can provide!
FYI...went back to single quotes " & r & " as you originally suggested, and debugging revealed r = "Group 1" with the type mismatch error remaining on line 25. So double quotes is not a thing...I think.
Thanks, Fluffinator! Attempted both options above, but both still result in error 13 type mismatch. On a whim, added double quotes as in line 25 below. This pushed the type mismatch error down to the next row (line 26). During debugging, I highlighted this portion of the code: "" & r & "" to see its result, and it revealed "" & r & "" = "Group 1", where Group 1 is the combo box drop down selection I made. So seems we are very close!
Debugging in Watch window for line 26 reveals Value = <Expression not defined in context> and Type = Empty
Looking for an assist to adjust this code sourced by Forum Guru Fluff13. As presented below, lines 25 & 26 successfully searches column 37 of my data table for the string "Group 1" and returns the rows and desired columns into array v.
I need to adjust line 25 to search for the selected value of ComboBox1 on a user form, instead of just "Group 1". CombBox1 holds a list of text strings (Group 1, Name 3, Team 7, etc). I dimmed r as a string variable to hold the combobox value, but I get a type mismatch error when trying to replace the "Group 1" text string with the variable r...with or without quotes or any other variable type. Any help would be much appreciated!Code
- Dim m&, n&, v, ws As Worksheet ' data types Long, Long, Variant, WorkSheet
- Set ws = ThisWorkbook.Worksheets("Sheet1")
- Dim vRws As Variant
- Dim r As String
- 19: r = Me.ComboBox1.Value
- 21: '  define Start Row m and Last Row n (based on items in column A) of the ListObjects(1)
- 22: m = 2: n = ws.Range("A" & ws.Rows.Count).End(xlUp).row
- 23: '  assign data to variant datafield array
- 24: With ws.ListObjects(1).DataBodyRange
- 25: vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=""Group 1"",row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).row))), "X", False)
- 26: v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 11, 12, 13, 29, 30, 34, 42)) 'column numbers from DataTable to bring into the listbox
- 27: End With
Error problem resolved with the above code.
Hello. I'm trying to transfer the single column list contents from a user form list box into an array, and then into a single column range on a worksheet. The code line (16) to resize the destination range throws an "application-defined or object-defined error" that I cannot figure out. I'm in over my head on this one and hoping someone can help me diagnose my problem. Many thanks!Code
- Sub DataTransfer()
- 'Transfer single column FilterHide listbox contents into an array, then into a single column range
- Dim sh As Worksheet
- Set sh = ThisWorkbook.Sheets("Transfer")
- Dim HideArray() As Variant
- Dim Dest As Range
- 'Clears the destination Target range
- 'Populates the Target range on the Transfer worksheet with the list of items in the lbFilterHide listbox on userform frmProfileSettings
- HideArray = frmProfileSettings.lbFilterHide.List
- Set Dest = sh.Range("Target")
- Set Dest = Dest.Resize(UBound(HideArray), 1) '<<<this row triggers a Run-time error '1004': Application-defined or object-defined error
- Dest.Value = Application.Transpose(HideArray)
- End Sub