UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Colored Cells    
 
   
KingMartin
post Feb 25 2004, 02:02 AM
Post#1


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


A friend of mine has asked me whether it was possible to filter columns according to the Interior colors of its cells. The decision about the colors was his subjective decision and it was not possible to implement any logic that would use the values in the cells for the filtering.
o I have written a small utility that is attached to this post. I find it quite useful and I am happy to share it with UA.
Kind regards
Martin
Attached File(s)
Attached File  FilterColored.zip ( 27.4K )Number of downloads: 223
 
Go to the top of the page
 
parry
post Feb 26 2004, 03:41 AM
Post#2



Posts: 125
Joined: 20-January 03
From: Wellington, NZ


Very nice Martin, youve obviously put a lot of work into this. smile.gif
How do you add new colours - it didnt seem to add it for me. Also, what does the AutoUpdate do? shrug.gif
Go to the top of the page
 
KingMartin
post Feb 26 2004, 04:15 AM
Post#3


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


Hello Parry,
Actually this post was meant for FAQs . But I am glad to learn where I was inefficient...
THere (Module1, Sub RefreshColors) I grab the colors from the column and stack them into myColors Collection:
CODE
Set shtActive = ThisWorkbook.ActiveSheet
With shtActive
    For Each cl In Intersect(.UsedRange, Columns(myCol))
        clr = cl.Interior.Color
        On Error Resume Next
        If clr <> 256 ^ 3 - 1 Then myColors.Add clr, CStr(clr)
        On Error GoTo 0
    Next cl
End With

Then later on in the same sub, I convert the Color to RGB and color the buttons. The buttons are added to the form in a design mode and the buttons for which is no color left remain unabled. There could be also added on the fly, but I like it more like it is. (I mean I am lazy to code the procedure, but in the module you'll find a proc that adds the 12 buttons - Sub Add12ToggleButtons ).
CODE
'Convert color to RGB
For Each clr In myColors
    Red = clr And 255
    Green = clr \ 256 And 255
    Blue = clr \ 256 ^ 2 And 255
    With UserForm1.Controls("tgl" & i)
        .BackColor = RGB(Red, Green, Blue)
        .Enabled = True
        .Value = False
    End With
    i = i + 1
Next clr

Autoupdate: I added this so that the filter is applied immediately if you click-unclick the togglebuttons. Sometimes it's convenient so, sometimes you want first to pick up the colors and then run the filter.
Martin
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 06:27 PM