List box: Hide Certain colums

[Yomi]

1.I have a worksheet with range" A1:H 1" which serves as the row source for
my list box. However, I dont want column B and A displayed in the list box.
This does not happen even if I hide the colums in the worksheet manually

2. In the same worksheet, I want to filter by Column B such that items not
part of the filtered range does not show in the list box. This does not
happen also in listbox

Can Someone please help me.
thank you in advance

 

[Dave Peterson]

#1. Why do you have to include columns A:B in the rowsource? Is there a
reason? If there is, then how about setting the columnwidth property to
"0;0;......" (hiding the two columns in the listbox).

#2. You'll have to drop the rowsource property and use .additem. Loop through
the range and only add the data on the row for the visible rows.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim vRng As Range
Dim myCell As Range
Dim iCol As Long

Set wks = ThisWorkbook.Worksheets("Sheet1")

With Me.ListBox1
.ColumnCount = wks.Range("a1:H1").Cells.Count
.MultiSelect = fmMultiSelectSingle '?????
.ColumnWidths = "0;0;25;25;25;25;25;25"
End With

With wks.AutoFilter.Range
Set vRng = Nothing
On Error Resume Next
'single column, exclude header and resize to exclude header
Set vRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

With Me.ListBox1
If vRng Is Nothing Then
'nothing visible, what should happen
.Enabled = False
Else
For Each myCell In vRng.Cells
.AddItem myCell.Value
For iCol = 1 To .ColumnCount - 1
.List(.ListCount - 1, iCol) = myCell.Offset(0, iCol).Value
Next iCol
Next myCell
End If
End With

End Sub

 

[OssieMac]

Set the ColumnWidths property of the list box. The column width of the
columns not to be seen set to zero. The column widths are entered in points
so for columns to be viewed do not use anything less than about 15-20 to
start with and then adjust as required after viewing the end result.

You can simply set the column widths as a numbers separated by commas and
Excel will then adjust with pt suffix and place semicolons between them.

Example: Enter the column widths as follows
15,0,30,30

Excel will adjust to following
15 pt;0 pt;30 pt;30 pt

 

[Yomi]

OssieMac, this is wonderful!. It worked for the column. However, for the
second part, the hiidden rows on the filtered range still show in the List
box.

Can you or anyone help please. I dont want hidden rows(when autofilter is
used) to show in the list box

Regards

 

[OssieMac]

Hi Yomi,

My apologies for not answering both parts of your original post. I am guilty
of not reading all of the question properly.

To use the filtered list in the list box, the visible cells of the filtered
list needs to be copied to another location.

The list in the new location needs to have a defined name and the defined
name used as the ListFillRange of the ListBox.

Next an event needs to be used to trigger the code to create the new list
and redefine the new range each time the Autofiltered list is changed. To do
this, create a subtotal formula on the worksheet and then each time the
Autofilter is changed, the subtotal updates and this will trigger a calculate
event. The subtotal formula is simply a dummy formula to force a calculate
event to trigger the code.

The subtotal formula can be placed on the worksheet over to the right out of
the way of all other data and then the subtotal formula can be used on an
entire column of the Autofiltered data. It must be on the same worksheet as
the AutoFiltered data. Therefore in say cell AA1 (or any cell out to the
right of the AutoFiltered list) insert the following formula which will count
the number of displayed rows in the Autofiltered list:-

=SUBTOTAL(3,A:A)

Insert a new worksheet and name it "ListBox Range" (without the double
quotes).

Copy the following code (between the asterisk lines) into the VBA Module for
the worksheet with the AutoFiltered list. (Right click the worksheet tab and
select View Code to open the worksheet module).

'***************************************

Private Sub Worksheet_Calculate()

Dim wsFilter As Worksheet
Dim wsListBox
Dim rngFiltered As Range
Dim rngList As Range

'Worksheet "Filtered Range" is the sheet
'with AutoFilter.Edit name to suit your
'worksheet name
Set wsFilter = Sheets("Filtered Range")

'Worksheet "ListBox Range" is a separate
'worksheet that can be hidden if desired.
Set wsListBox = Sheets("ListBox Range")
wsListBox.Range("A").Clear
With wsFilter.AutoFilter.Range
Set rngFiltered = .Offset(1, 0) _
.Resize(.Rows.Count - 1, 4) _
.SpecialCells(xlCellTypeVisible)
End With

rngFiltered.Copy _
Destination:=wsListBox.Cells(1, 1)

With wsListBox
Set rngList = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, "D").End(xlUp))
End With

'Define a name for the
rngList.Name = "ListBoxFill"
End Sub

'****************************************

Edit "Filtered Range" in the following line of code to match your worksheet
name with the AutoFiltered list.

Set wsFilter = Sheets("Filtered Range")

Close the VBA Editor.

Make a change to the Autofiltered list. (The code will run but you should
not see anything occur with the ListBox at this stage because the Defined
name has not been inserted into the ListBox properties).

Now select Design and edit the ListFillRange of the List Box to
"ListBoxFill" (without the double quotes).

Close Design view and now each time you change the Autofilter, the ListBox
should update.

The reason for forcing the event code to run once before entering the
defined name against the ListFillRange in the ListBox is because the name
must be defined before you can use it in the ListBox property and this is an
easy way to do it.

Feel free to get back to me if you have any problems with it however, it
will be 24 hours after this post before I am likely to answer.