Filters and combo boxes

Martel

New Member
Joined
Jun 19, 2019
Messages
26
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Post the code between code tags that does it. Click the # icon on reply toolbar to insert tags.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top