Filters and combo boxes

[Martel]

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?

 

[Kenneth Hobson]

Post the code between code tags that does it. Click the # icon on reply toolbar to insert tags.

 

[Martel]

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.

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.​