• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba button sort value1, value2 or reset

L

l_mirica

New Member
Hello,

First of all, I have a table that looks like this:

Head1 Head2 Head3 ...
1 1 2 ...
2 1 2 ...
1 1 1 ...
... ... ..... etc.

Since I am using this kind of table every single day, I thought that I could buy myself (and others) a little bit more time by having one vba button for each header that would sort the corresponding column by just one click.

To be more specific:
Click1 on the Head1 will sort the column and show only the entry "1"
click2 on the same button will show only values of "2"
Third click shall reset the column, bringing it to the initial state.

And for each column, I shall have one button. It's a large table and it a real pain to check only one value or another ...


Thank you so much for your trouble !
We are here to learn, to teach and to exchange ...
 
Hi, l_mirica!

Try something like this code, it sorts a worksheet from a single command button using the selected cell as key column:
Code: 
Option Explicit

Private Sub cmdSort_Click()
    ' constants
    ' declarations
    Dim I As Integer
    ' start
    ' process
    I = 0
    On Error Resume Next
    I = ActiveCell.Column
    On Error GoTo 0
    With ActiveWorkbook.Worksheets("Functions").Sort
        .SortFields.Clear
        If I > 0 Then
            .SortFields.Add Key:=Columns(I), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End If
        .SortFields.Add Key:=Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Cells
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ' end
End Sub

Update it as required and just advise if any issue.

Regards!
 
1. It seems I do not know how to use this - since I do not see it in the macro list, I can not put it into a button and it is not a "before double click action" or some sort

2. forgot to mention: when I click one button, the column will remain with only values of "1" and after that I would click the other's column's button and the table would get filtered again.

to keep it to my exemple, I want to click on the button from Head3 - twice -> that will leave me with the following table: (once it will show "1" and second will show "2")

Head1 Head2 Head3(2nd click) ...
1 1 2 ...
2 1 2 ...

now if I click 2 times on Head1 I will get:
Head1 Head2 Head3 ...
2 1 2

BTW - I do not know if this will have any impact on the result but just to make assure a smooth transition: the headers are situated in the row 2:2.

Thank you for your patience !
 
Hi, l_mirica!
Consider uploading a sample file, but:
1) It'd be the code for the click event of a command button
2) I hope it'd get clearer with the uploaded file
Regards!
 
ok, I have made a book and uploaded.
now, from left to right, first sheet contains an example of table that needs sorted.
the rest of them are colored as follows: the color of the sheet is the same as the color of the column on which the action "took place".

so imagine that instead of one click to the next sheet you would click one time on the colored header.
for the time being that was the best thing I could think of.

PS - to reset the whole table I do not need "one click action", I can just easily loop trough all the filtered columns or just use the "CTRL+SHIFT+L" feature
 

Attachments

  • vba-button-sort-value1-value2-or-reset.xlsx
    15.7 KB · Views: 10
Hi, l_mirica!

I think I understand it now, I'll try to come back late today with something.

One question: why worksheet Click3 reset filter of column C instead of don't displaying any filtered cell (filtering for a 3 value)?

Regards!
 
it can be a 3d value but I am only interested in 2. that was just as an example so that, if I have many filtered columns, I should be able to cycle through one single column once again to check stuff up (or if already filtered N columns and one wrong click - in just a couple more clicks it's all back to normal, without the need to reset the whole filter)
 
yes :p but control on every column

so I just need that piece of code to put in every button over the header, instead of that of the "sort" one

cheers! I have a very poor communication skill ! :(
 
Hi, l_mirica!

Give a look at this file:
https://dl.dropboxusercontent.com/u...2-or-reset (for l_mirica at chandoo.org).xlsm

It has 3 dynamic named ranges (TitleList for row 2, ClickList for row 1 and DataTable for rows 3 in advance). There's a cyan reset command button and a transparent command button on each table header column, except for the filter arrow area. ClickList range is formatted with white font and holds the value of each column click count.

This is the code:
Code: 
Option Explicit

' global constants
Const gksData = "DataTable"
Const gksClick = "ClickList"

' global declarations
Dim grngC As Range, grngD As Range

Private Sub CommandButton0_Click()
    ' constants
    ' declarations
    ' start
    Set grngC = Range(gksClick)
    Set grngD = Range(gksData)
    ' process
    grngC.ClearContents
    With grngD.Parent
        If Not .AutoFilter Is Nothing Then
            If .FilterMode Then .ShowAllData
        End If
    End With
    ' end
    Set grngD = Nothing
    Set grngC = Nothing
    Beep
End Sub

Private Sub CommandButton1_Click()
    CommandButtonClick 1
End Sub

Private Sub CommandButton2_Click()
    CommandButtonClick 2
End Sub

Private Sub CommandButton3_Click()
    CommandButtonClick 3
End Sub

Private Sub CommandButton4_Click()
    CommandButtonClick 4
End Sub

Private Sub CommandButton5_Click()
    CommandButtonClick 5
End Sub

Private Sub CommandButton6_Click()
    CommandButtonClick 6
End Sub

Private Sub CommandButton7_Click()
    CommandButtonClick 7
End Sub

Private Sub CommandButtonClick(piCommandButton As Integer)
    ' constants
    Const kiCycle = 2
    ' declarations
    Dim I As Integer
    ' start
    Set grngC = Range(gksClick)
    Set grngD = Range(gksData)
    ' process
    '  click count
    With grngC.Cells(1, piCommandButton)
        .Value = ((.Value + 1) Mod (kiCycle + 1))
        I = .Value
    End With
    '  filter?
    With grngD
        Select Case I
            Case Is = 0
                .AutoFilter Field:=piCommandButton
            Case Is > 0
                .AutoFilter Field:=piCommandButton, Criteria1:=CStr(I)
        End Select
    End With
    grngC.Cells(1, piCommandButton).Select
    ' end
    Set grngD = Nothing
    Set grngC = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!
 
simply mind blowing but as I feared ... I still forgot something.
that table needs to be dynamic so, if I add or remove data from it - or just change all data (but always keeping the headers) I need it to work.

So, it needs a little adjustment - Insert > Table (My table has headers) because now it does not fully work that way.


Anyway, it's simple and spectacular. I'll take the time to study it and understand how and why it works.
 
Hi, l_mirica!

But actually it's absolutely dynamic! Check the dynamic name definitions from the name manager:
TitleList: =DESREF(Data!$A$2;;;1;CONTARA(Data!$2:$2)) -----> in english: =OFFSET(Data!$A$2,,,1,COUNTA(Data!$2:$2))
ClickList: =DESREF(TitleList;-1;) -----> in english: =OFFSET(TitleList,-1,)
DataTable: =DESREF(Data!$A$3;;;CONTARA(Data!$A:$A)-1;COLUMNAS(TitleList)) -----> in english: =OFFSET(Data!$A$3,,,COUNTA(Data!$A:$A)-1,COLUMNS(TitleList))

The only constraint is that if you add new columns you should copy the command button and place the code for the click event, just that.

Play a bit adding data and come back to tell us if you succeeded or not.

Regards!
 
pfiu ... finally I've got it to work (I had to modify my book) ;)

thank you so much for your help. since i've struggled to "transpose" it into my work, I am starting to understand it.

had a bit of struggle cuz instead of 1 and 2 values I had yes and no and since now there are some ppl who watched the writing and not the color (green and red) ... kind of have to return to my previous form.

anyway, I am sure it would have taken me a few more months until I would have managed to do myself something similar.

simply brilliant, many, many thanks !
 
Just wanted to let you know that I've made the required modification and now I (I mean WE) use this new piece.
Code: 
    Dim I As String
   '...........................
            Case Is = 0
                .AutoFilter Field:=piCommandButton
            Case Is = 1
                I = "Yes" ' added to change criteria
                .AutoFilter Field:=piCommandButton, Criteria1:=CStr(I)

Thank you SO MUCH !!! I hope one day I'll get to help others !:rolleyes:
 
Hi, l_mirica!

About your changes:
a) Variable I was defined as integer as it was intended to hold the click-cycle status (0 = 0 clicks, 1 = 1 click, 2 = 2 clicks, 0 = 3 clicks -via modulo function-)
b) Now if you change it to string clicking on header command buttons makes autofilter don't work properly for the upward cycle and just works for the 1st case (0, even if string), i.e., for resetting filter. You should replace your method to work with strings instead of integers, and the smart part of the job was the modulo which will now be unavailable.
c) You'd have to think in a new cycle like "", "Yes", "No", or something alike; then use arrays as modulo won't do the job.

However, if it works for you everything's fine. I just want to state this clearly for other people who might come in here and read this thread: for numeric use I insist on my version, for string use it'd be helpful if you upload your full version modified file instead just a few lines of code.

Regards!
 
then leaving =I= declaration to =Integer=, just changed:

Code: 
        Select Case I
            Case Is = 0
                .AutoFilter Field:=piCommandButton
            Case Is = 1
                .AutoFilter Field:=piCommandButton, Criteria1:="Yes"
            Case Is = 2
                .AutoFilter Field:=piCommandButton, Criteria1:="No"

this also works for me except 1st column (do not yet know why) but I'll keep insisting !
 
Hi, l_mirica!

That would do the job, while keeping I for cycling 0-1-2 and replacing "Cstr(I)" by "Yes" and "No". My previous comment of an array:
sArray = {"", "Yes", "No"}
pointed to the same structure, but replacing "CStr(I)" by "sArray(I)".

Regards!
 
Once again I would like to thank you so much for your help !

I have discovered where my first column sorting has the issue - once along the way I have made a poor setting and tried to replicate the whole thing (to see if excel has a problem)

looks like it did exactly the same so I went and recreated the whole thing from the scratch and now it works flawlessly!

:) ;) :cool: :p :DD

PS: I am trying not to ask for exactly what I need but for what I may manage to adapt to my needs (not looking for "fast food") so thank you for your patience and support ! Cheers !
 
Hi, l_mirica!

That's a clever way to learn, just eat what being able to digest... :)
However, McDonalds, Wendy's and Burger King aren't fast food... :rolleyes:

Regards!

PS: More slow than fast, if you consider the digestion time :p
 
You must log in or register to reply here.
Back
Top