Free Microsoft Excel 2013 Quick Reference

AutoFilter _FilterDatabase Rowsource Listbox

Excel 2000

I am trying to get AutoFilter data into a ListBox.
The below code gives me an "Type mismatch" error when I try to add the
Listbox Rowsource line. I read about using _FilterDatabase but could
not get the right syntax.

Private Sub UserForm_Initialize()
Dim myVisibleRng As Range
Dim myFilterRng As Range
Set myFilterRange = Sheet1.Range("a1:e13000")
myFilterRange.AutoFilter Field:=5, Criteria1:="18650"
Set myVisibleRange = myFilterRange.SpecialCells(xlCellTypeVisible)
Debug.Print myVisibleRange.Address
ListBox1.RowSource = myVisibleRange '


Post your answer or comment

comments powered by Disqus
I have the following range set as the rowsource in my listbox:

Dim rng As Range
lastrow = Cells(1, 1).End(xlDown).Row
Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastrow, 2))
Me.ListBox1.RowSource = rng.Address

Cells in column 3 have either a "Yes" or a "No" value inside them. Is there
a way for me to filter the range ("rng") so that the listbox only shows rows
with a "yes" in column 3? I tried running an autofilter, but the listbox
still included all cells in rng.

In addition, is it possible to show two non-adjacent columns in a listbox
(ie column A and column C, but not column B)? If so, how?

Thanks!

Hi all,

I am still very new to all this VBA stuff, and would really appreciate some assistance.

Using Excel 2003, In the piece of code below extracted from the full code, I am autofiltering the "Prices" worksheet from two input cells (G4 & G6) in the "Trial Criteria" worksheet.

I would like to change the 2 x input cells in the Trial Criteria" worksheet to ListBoxes, but cannot work out how to set Criteria1 below to pick up the selected result from the 2 ListBoxes (of, say 6 items each). It will only be a single, not multiple, selection from each of the two ListBoxes.

Could anyone kindly point me in the right direction ?

many thanks in advance.

Kim


	VB:
	
Sheets("Prices").Select 
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("Trial Criteria").Range("G4") 
Selection.AutoFilter Field:=3, Criteria1:=Worksheets("Trial Criteria").Range("G6") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Excelians

I have listbox with 1 column source range B6:B25
Some time I want to remove some items before print is there possible to remove such item from listbox as well as corresponding cell without deleting row

So I have several employees all with their own individual spreadsheets. They all have the same client, vendor, and staff lists in their spreadsheets. When I need to update the lists I have to go to everyones individual spreadsheet and this is time consuming.

Is there a way for me to have one database with all that info and have the individual spreadsheets reference that database?

This is all being done via a UserForm with a ComboBox and that is why I referenced RowSource.

Board,

Having spent several hours (6!) experimenting and searching the board for a solution but this vb newbie is stuck.
I have created a userform, combobox1 returns values from the first column of my named range 'database', I'd like the AddressLine1 textbox(*) to return the value of column 7 from the 'database' range corresponding to the choice selected in combobox1.

RowSource, Listbox, BoundColumn, TextColumn ?????

(*) currently a text box because if this is a new record the user will need to type the info in

Please help, I type through tufts of hair gripped tightly between my fingers,

Thanks
bob

Hello,

I have a listbox that populates from a sheet. I have a button that removes the selection from the list box. I am running in to a problem:

If you select the last item in the listbox and click remove, it only removes the item from the first column and gives an (application defined or object defined) error. It then highlights a range delete line, and listindex is equal to -1.

If anyone could help me with this, I would greatly appreciate it! Thanks in advance!

Here's my code:

	VB:
	
 TeamList_DropButtonClick() 
    With TeamList 
        .AddItem "Team 1" 
        .AddItem "Team 2" 
        .AddItem "Team 3" 
        .AddItem "Team 4" 
        .AddItem "Team 5" 
        .AddItem "Team 6" 
        .AddItem "Team 7" 
        .AddItem "Team 8" 
        .AddItem "Team 9" 
         '.AddItem "Team 10"
    End With 
End Sub 
 'Populate ListBox when Team is selected
 '-----------------------------------------
Private Sub TeamList_change() 
     'Enable the Add/remove employee buttons
    Me.CommandButton2.Enabled = True 
    Me.CommandButton3.Enabled = True 
    Sheet13.Select 
     'clear all previous data from "temp"
    Columns("A:A").Select 
    Selection.ClearContents 
    Columns("B:B").Select 
    Selection.ClearContents 
    Columns("C:C").Select 
    Selection.ClearContents 
    Columns("D:D").Select 
    Selection.ClearContents 
    Columns("E:E").Select 
    Selection.ClearContents 
     
    Sheet7.Select 
     'Remove filters from sheet
    ActiveSheet.ListObjects("Table_SPSQL1_Attendance_View_Active_Employee_WG"). _ 
    Range.AutoFilter Field:=6, Criteria1:="Plant 2 - Corunna Rd." 
    ActiveSheet.ListObjects("Table_SPSQL1_Attendance_View_Active_Employee_WG"). _ 
    Range.AutoFilter Field:=8 
     
    With Sheet7 
         'Apply Filters by team number
        With .Range("H1") 
             
            .AutoFilter Field:=8, Criteria1:=TeamList, Operator:=xlAnd 
        End With 
         
        Dim Lastrow As Long, rngSource As Range 
        Dim rnglist 
         'Select visible data
        Lastrow = Sheet7.Cells(Rows.Count, 1).End(xlUp).row 
         
        Set rnglist = Sheet7.Range("C2:D" & Lastrow).SpecialCells(xlCellTypeVisible) 
    End With 
     
    Sheet13.Select 
     'Copy visible data to "temp" sheet
    With Sheet13 
        Sheet13.Range("C1:E" & Lastrow).CurrentRegion.Resize(, 1).ClearContents 
        rnglist.Copy .Range("C1") 
        Sheet7.Range("I2:I" & Lastrow).SpecialCells(xlCellTypeVisible).Copy .Range("E1") 
         
         'Set rowsource of listbox
        Me.ListBox1.RowSource = .Range("C1").CurrentRegion.Address 
        Set rngSource = Nothing 
         
    End With 
End Sub 
 
 'Remove Employees from ListBox
 '---------------------------------
Private Sub CommandButton3_Click() 
    On Error Goto blah 
    Sheet13.Select 
    Dim lastrows As Long 
    lastrows = Sheet13.Cells(Rows.Count, 3).End(xlUp).row 
    Dim strRange As String 
    With ListBox1 
        strRange = .RowSource 
        Range(strRange).Cells(.ListIndex + 1, 1).Delete Shift:=xlUp 
        Range(strRange).Cells(.ListIndex + 1, 2).Delete Shift:=xlUp 
        Range(strRange).Cells(.ListIndex + 1, 3).Delete Shift:=xlUp 
        .RowSource = vbNullString 
        .RowSource = strRange 
    End With 
     'Refresh the rowsource of listbox
    Me.ListBox1.RowSource = Sheet13.Range("C1:E" & lastrows).CurrentRegion.Address 
    Goto noblah 
    blah:  MsgBox "You have not selected an employee" 
    Exit Sub 
noblah: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


G'day there once again, One & All,

I'm currently trying to finish off a userform which has a
multicolumn ListBox that has a dynamic range as a rowsource. I've
managed to get most of it working the way I want it too, my columns
display as intended, and sort themselves according to selections made
via optionbuttons. I even have a sort option for ascending & descending
order.

What I want to do is give my users the option of displaying data
that only belongs to a user defined category. Eg, click the "OSA" option
button and the listbox will display only those employees who are paid
Operational Shift Allowance, etc.

I tried several variations of Autofilter before looking on the net
& finding that filtering the list has no effect (something I'd already
seen for myself). I then had a few goes at using combinations of
range.rows.hidden, and another method I can't recall at the moment, but
which had a criteria of xlcelltype = visible.

Having no success there, I tried copying the visible data from my
range, and then pasting it to a different location. The problem then was
that I couldn't (and still can't) reset the ListBox's rowsource range.
It gives me a 1004 error when I try to point it at the copied/pasted
data.

I've not tried the .additem method because I've set the rowsource
earlier in the code and "Help" tells me that I can't use it when the
rowsource is assigned.

I've no doubt this has occurred previously and there has to be a
workaround, but I can't find it. Google searches with various criteria
bring me plenty of hits, but nothing that I've found useful so far.

Can any one please offer advice, or a site or reference that might
explain house it's done?

Thank you once again,
--
Ken McLennan
Qld, Australia

G'day there once again, One & All,

I'm currently trying to finish off a userform which has a
multicolumn ListBox that has a dynamic range as a rowsource. I've
managed to get most of it working the way I want it too, my columns
display as intended, and sort themselves according to selections made
via optionbuttons. I even have a sort option for ascending & descending
order.

What I want to do is give my users the option of displaying data
that only belongs to a user defined category. Eg, click the "OSA" option
button and the listbox will display only those employees who are paid
Operational Shift Allowance, etc.

I tried several variations of Autofilter before looking on the net
& finding that filtering the list has no effect (something I'd already
seen for myself). I then had a few goes at using combinations of
range.rows.hidden, and another method I can't recall at the moment, but
which had a criteria of xlcelltype = visible.

Having no success there, I tried copying the visible data from my
range, and then pasting it to a different location. The problem then was
that I couldn't (and still can't) reset the ListBox's rowsource range.
It gives me a 1004 error when I try to point it at the copied/pasted
data.

I've not tried the .additem method because I've set the rowsource
earlier in the code and "Help" tells me that I can't use it when the
rowsource is assigned.

I've no doubt this has occurred previously and there has to be a
workaround, but I can't find it. Google searches with various criteria
bring me plenty of hits, but nothing that I've found useful so far.

Can any one please offer advice, or a site or reference that might
explain house it's done?

Thank you once again,
--
Ken McLennan
Qld, Australia

I am trying to find a way to use a filtered range as the Rowsource for a
listbox
I hoped to use AutoFilter to provide the data but it doesn't seem
appropriate.
Will I have to copy the filtered data to another area on the worksheet ?
.... or is there a better way to achieve the desired result ?

Regards & TIA

Hello,

I am trying to create a ListBox that displays the previous 10 entries in Columns A through G from a sheet called "log." I can't seem to get it work right. Here's the Code, I get the debugging error on the line to set the row source. Any Ideas?


	VB:
	
 Userform_Activate() 
     
    Sheets("Log").Select 
    Dim lastrow As Long 
     
    If WorksheetFunction.CountA(Cells) > 0 Then 
         
         'Search for any entry, by searching backwards by Rows.
         
        lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row 
         
    End If 
     
    ListBox1.RowSource = Range("(lastrow-10,1):(lastrow,7)") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in Advance!

I have three listboxes and I set each of their row sources from code in UserForm_Initialize() event like so:


	VB:
	
 Sheet1 
    lbxLanguages.RowSource = .Range("C2", .Range("C2").End(xlDown)).Address 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the form, sometimes I see data in two of the three listboxes and the third has blank/empty rows; sometimes all three listboxes have only empty rows in them (i.e. correct number of checkbox squares displayed but no data in front of the squares).

Can someone please shed light on this. I'm stumped!

Hi all,

This is my first post here so be please be patient with me. My VBA is limited as I only use it now and again.

I'm trying to create an archiving system for old documents and templates of documents using excel.

Basically on a Sheet called 'Home' I will have an interfaces with two listboxes (Lets called them set A) that users can access. These will sort a list of documents on a hidden sheet called 'Feed' using an autofilter. I want the results of the autofilter to then populate onto another 2 listbox (Set B) called Templates and Examples respectively.

I am able to get the autofilter to sort itself using Set A's listboxes but when it tries to populate onto Set B's interfaces it only populates anything before a hidden row. e.g If you Click 'Corporate Partner' and 'Risk and Issue Log' then in the templates listbox it only shows Temp #1 and Temp #2. It does not show Temp #3. How do I overcome this? I'm assuming I use a loop or something to carry on searching once it finds a hidden row?

Once this is done the user will then be able to use Set B's listboxes to open those documents.

I've attached my work so far. I haven't finished it yet so obviously there are bits that still don't work. I am happy to post my work on this forum once I'm done for others to make use of it.

thanks for your help in advance.

Stephan

Greetings all.

I have a UserForm containing a ComboBox and a ListBox. One chooses an account from the Combobox, clicks a command button to populate the ListBox with the locations associated with that account, and now I want to use the locations selected to autofilter a worksheet in the workbook. The number of locations vary by account so I think I'll need a dynamic array, but I'm not sure how to create one and use it as the criteria in my autofilter. Using code I've found on this forum, I have:

Dim vLocs() As Variant
Dim lUpper As Integer
Dim lLoop As Integer

With UserForm1.locations
For lLoop = 0 To .ListCount - 1
If .Selected(lLoop) Then
lUpper = lUpper + 1: ReDim Preserve vLocs(1 To lUpper)
vLocs(lUpper) = .List(lLoop, 0)
End If
Next lLoop

For lLoop = LBound(vLocs) To UBound(vLocs)
'I think this would be the part where I'd name the dynamic array

Next lLoop
'I'd have my autofilter code here I think
End With

Any suggestions would be great. Thanks!

Hi, i am a beginner in Excel VBA programming. I found that my listbox in userform cannot update automatically(instead it change to blank) when a new data is insert to the target cell which is rowsourced to the particular listbox. But when i close and reopen the userform, the data which i added just now appear in the listbox.I rowsource the listbox from its properties, the command are: 'sheet1'!A2:E5. Really appreciate if anyone can help me.

Best Regards,
Alext

Hi
How can i populate a forms listbox from another workbooks named range?
i have been trying to get this to work

	VB:
	
lstBeamsBulkheads.RowSource = Workbooks("DataPricing").Range("BeamsBulkheads") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
cheers
toe

Is there a way I can set the rowsource of a Listbox on a form to show specific data dependant on what I enter in a text box.

For example, if I had a spreadsheet with Name, Gender, Age in row A1 as the headings could I set the rowsource of the listbox to show all the people whos age was '26'.

I am more familiar with Access VBA, in which I would use code like:

Listbox.Rowsource = "SELECT * FROM table WHERE age LIKE'" & txtAge & "'"

can anyone help please, i can't seem to convert this code to work in Excel VBA.

Cheers

Hi I am a newbee to this site and trying to improve my knowledge and coding.
I have a worksheet with various items in each column. This code filters the selected listbox item (listbox11) and shows the results in another listbox (listbox13). How do you filter by 2 listbox values example listbox11 and say another listbox12 and display results in listbox13. Of course if user only selects item from one listbox it would also filter by that one box as it is currently doing.


	VB:
	
 CommandButton12_Click() 
    Dim sCriteria As String, rngList As Range, sRngAddress As String 
     
    sCriteria = Me.ListBox11.Value 
     
    With Sheet1 
        .AutoFilterMode = False 
        .Range("A5:g100" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=1, Criteria1:=sCriteria 
        Set rngList = .Range("A5:g" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible) 
        Sheet3.Cells.ClearContents 
        rngList.Copy Sheet3.Range("a4") 
        sRngAddress = "Sheet3!" & .Range("a5").CurrentRegion.Address 
    End With 
     
    With Me.ListBox13 
        .RowSource = sRngAddress 
    End With 
     
    Set rngList = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many Thanks

I cannot find the field to specify RowSource in the UserForm properties for combo box or listbox in Excel for Mac 2011. Is this hidden for some reason? If it's not there in the properties box, how do I specify the Input Range/RowSource for such boxes inside a UserForm?

Hi Chaps... a problem and a solution (after a 6 hour wild goose chase).

I was getting a random "Run-time error '-2147467259 (80004005)': Unspecified error. when I was setting the rowsouce for a listbox... I tried all the usual suspects like an invalid range etc etc. but no luck.

After numerous google searches I didnt find any solutions, so I said I would briefly share what I found and think I solved.

The crux of the problem is that I had two listboxes on two different forms using the same RowSource - which was a defined named named range ("MyList"). I had typed it in manually into the properties panel for each of the listboxes.

The solution was to remove the manually typed named range from the rowsource properties dialog for each listbox and define them programmatically instead. The key to the code was that the row sources for each listbox had be switched off before setting the rowsource for the other listbox. e.g. (in the userofrm initialise event, for example)

	VB:
	
frmOne.listbox1.rowsource = "" 
frmTwo.listbox2.rowsource ="MyList" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And the reverse of this in the userform that contained the other listbox.

As a side note, I ran into further difficulties because each listbox was on a seperate form and setting the rowsource for the listbox on the form triggers the initialise event for that form - so I had to use a global boolean variable to stop the initialise event that contained that listbox from running through. Kinda hard to explain, but I hope this information triggers some thoughts for other people who come across the same rowsource issue.

I STILL dont know why a common Rowsource across two different listboxes and two different userforms caused so many problems. So far as I can tell, neither forms were ever load into memory at the same time, but thats only so far as I can tell... Lord only knows what excel was actually doing. Problem solved now anyway.

Cheers
Ger

Gents
I'm hoping for advice and direction on speeding up my project

I am using a piece of code to autofilter and display records in a listbox on a userform using the following to send the filtered data to a temporary worksheet where it updates a dynamic range (dynamic range set up as per instructions on this sites tutorial, many thanks for that ) which is set as the rowsource for the listbox.


	VB:
	
 CommandButton1_Click() 
     
    With Application 
        .ScreenUpdating = False 
    End With 
     
     
    Sheets("Jobcards").Select 
    Selection.AutoFilter Field:=3, Criteria1:=combobox1.Value, Operator:=xlAnd 
    Cells.Select 
    Selection.Copy 
    Sheets("temp").Select 
    Sheets("temp").Activate 
    Range("A1").Select 
    ActiveSheet.Paste 
    Range("C:C,D:D,G:G,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,S:S,T:T,U:U").Select 
    Selection.Delete Shift:=xlToLeft 
     
     
     
    With Application 
        .ScreenUpdating = True 
    End With 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Unfortunately the updating of the listbox is fairly slow as excel recalculates my worksheets. If I set calculation to manual the dynamic range doesn't update. I have reduced the number of formulas and lookups as far as practical and that has helped but if somebody could suggest a better approach it would be appreciated.

Regards

Simpo

I am trying to use autofilter to fill a listbox in a Userform. The critieia is taken from the selection of a Combox value, I have posted the code below, it keeps erroring out and I cant understand why!

Private Sub PALM_NAME_CB_Change()
myvalue = PALM_NAME_CB.Value
Sheets("Stock & PALM ISIN").Select
With ActiveSheet
.AutoFilterMode = False
.Range("H1:I1").AutoFilter
.Range("H1:I1").AutoFilter field:=2, Criteria1:=myvalue
End With
Range("i3").Select
Selection.End(xlDown).Select
Range(Selection, "i3").Select
ActiveWorkbook.Names.Add Name:="PALM_ISIN", RefersToR1C1:=Selection
PALM_ISSUE_LB.RowSource = "PALM_ISIN"
End Sub

Post: Autofilter and Listbox how to acomplish?
-----------
I used the listbox from the control toolbox toolbar (ActiveX controls) and
put it on sheet1. Then I used this code behind the worksheet.
Option Explicit
Private Sub Worksheet_Activate()
Dim wks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long
Set wks = Worksheets("sheet2")
Set rng = wks.AutoFilter.Range
With rng
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
..Cells.SpecialCells(xlCellTypeVisible)
End With
With Me.ListBox1
..Clear
..ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Value)
For iCtr = 1 To rng.Columns.Count - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End Sub
If you filter the data on sheet2, then go back (and activate sheet1), the
listbox gets updated.
-------
What I would like to know is how to manipulate this onto a UserForm and to
use all the data on a worksheet called "Masters", Column Labels are A11,
data range is A2200.
I have a combobox that has a list of names on it and I would like to do a
autofilter type setup based on the name picked out of the combobox to appear
in the listbox for just that person.
I tried to follow this code ( i am somewhat new at this ) and I could not
figure out how to make it fit into a userform situation.

I tried to do a :
Private Sub Combobox1_Change()
If Combobox1.value = "Jeremy" then
Range("a1").select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Combobox1.value
--> after here I have tried to do Listbox1.list = range("A2200"), i have
tried Listbox1.rowsource("A2200"), etc.. everything always kept coming
back with some sort of error or would list all the cells
with in the listbox.

Does anyone have any ideas for me? I would appreciate the help.

I need help! What comes after "userform1.listbox1.rowsource = " I
have a named set of cells and I want the listbox to show the named set
of cells on the active sheet. Do I need to post more information???

This is what I have in VB:

Sub SelectItemsActive()
UserForm1.ListBox1.RowSource = "$ActiveSheet!DB"
UserForm1.Show
End Sub

Hi:

Can someone please tell me how to do the following?:
I need to specify a multicolumn range in a listBox (RowSource). I wan
to specify B6:G?. The first cell of the range is B6. The last cell o
the range will be an unkown row in column G. The number of rows wil
continue to grow with time. How do I specify B6:G? whereas the
represents an infinate row #?

Thank you in advance for your help

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements


No luck finding an answer? You could always try Google.