Filters and combo boxes

Martel

New Member
Joined
Jun 19, 2019
Messages
22
Small spreadsheet with 3 columns - first name, surname and status. Status can be any of several options including “withdrawn”. Filter to hide rows with “withdrawn” status works fine. Rows disappear as intended and filter is still in place if workbook is closed and re-opened. Problem - i am building a combo box for names to allow row selection BUT combo box is adding rows for names which are supposed to filtered out. Any ideas where I am going wrong?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Post the code between code tags that does it. Click the # icon on reply toolbar to insert tags.
 

Martel

New Member
Joined
Jun 19, 2019
Messages
22
Hi. The original example with 3 columns was submitted simply to demonstrate the point. The actual spreadsheet contains more columns. As part of a personal system, some of these additional columns contain potentially sensitive information for example, sexual orientation.
The intention was that only authorized users i.e. managers would have access to all records including those marked as withdrawn. Hence the plan to filter or hide. However, as previously mentioned this does not work. I wrote 2 small procedures to either filter out or alternatively hide ‘withdrawn’ records. Withdrawn, under Status can of course include options such Grade, Retired, volunteer etc. but in this case is being used to ‘mask’ sensitive records. I have now programmatically found a solution to what I see as an Excel anomaly.
I modified the original combobox_On change procedure to manually filter out records where status is marked as withdrawn but which Excel otherwise ignores. This works very well. But the trouble is it is too good in that no one will now see the filtered or hidden records, including those who should.
Solutions. Because eXcel knows the current user – “Application.username” I have created a dynamic named range listing the names of users with authorized access to all records. I have also set a Boolean variable in a code mode, initially set to false. The current user is checked against the authorized user list and if found then the Boolean is set to True and code responds accordingly, using a separate Code module for the combobox_On change procedure - see code examples.
None of this should be necessary. If eXcel respected its own filtering or hidden row conditions. Thank you.
Code:
Private Sub cbxname_Change()
‘ this procedure fills combo box with Firstname and Surname in that order – management preference - but still ‘finds’ based on surname - separate on click procedure
Dim lastrow As Long, mychoice As Long
Dim fn As String, ln As String, box As String
changed = changed + 1
If authorised ‘Boolean value
                Then
   Call allowed
                ‘ allowed procedure is the same except IF loop looking at “withdrawn” status is commnented out to allow all records to be added to combobox
  Exit sub
Else
lastrow = Range("Surname", Range("Surname")).Rows.Count
For Each strchoice In Range("surname")
    If Cells(strchoice.Row, Range("Type").Column).Text <> "withdrawn" Then
        If LCase(Left(strchoice, 1)) = Me.cbxname And Me.cbxname <> "" Then
            y = strchoice.Address
            x = Range(y).Offset(0, -1).Address
            Z = Range(y).Offset(0, -3).Address
            fn = Range(x).Text
            ln = Range(y).Text
            myid = Range(Z).Value
                ‘ myid is used for fast find in ID# number column
            box = fn & " " & ln
            Demo.cbxname.AddItem
            Demo.cbxname.List(ind, 0) = myid
            Demo.cbxname.List(ind, 1) = box
            ind = ind + 1
         End If
   End If
Next
Me.cbxname.DropDown
End Sub
Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,111
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top