Userform help needed to replace current one with a newer one

[DeezNuts]

I currently have one built with the macro that control true or false output but want to customize it a bit and cant see how current piece of code for the form is

    If MsgBox("Do you want to sort grades ascending or descending?" & vbCrLf & vbCrLf & "Click [Yes] for ascending" & vbCrLf & "Click [No] for descending", vbYesNo, "Sort by?") = vbYes Then
        sortAscending = True
    Else
        sortAscending = False
    End If
<code></code>

How would I go about making it look like this

With the output of true or false to control the macro.


Also instead of a short cut key is there a way to add a button or link to a sheet to activate the macro popup?

 

[DeezNuts]

Thank you Fluff after following these directions it only works in one directions from largest to smallest. Should work both ways.

 

[Fluff]

I forgot to change the SortAscending variable so, at the top of the main code change this

Public sortAscending As [COLOR=#ff0000]Boolean[/COLOR]

then on the ascending button change

sortAscending = [COLOR=#ff0000]True[/COLOR]

and on the descending button change

sortAscending = [COLOR=#ff0000]False[/COLOR]

If this doesn't work I'll have another look on Monday
HTH

 

[DeezNuts]

This thing is being a pain in the **** so you know what I have done just in case I have messed up is this to date

In UserForm1
Button 1

Private Sub CommandButton1_Click()
sortAscending = True
Call SortAreas
Unload Me
End Sub

Button 2

Private Sub CommandButton2_Click()
sortAscending = False
Call SortAreas
Unload Me
End Sub

Button 3

Private Sub CommandButton3_Click()
Unload Me
End Sub

In the Module1 I have

Option Explicit
Public sortAscending As Boolean

Sub UsrFrmShow()
    UserForm1.Show
End Sub

Sub SortAreas()
    Dim i As Long, j As Long, startRow As Long, areaRows As Long, areaCols As Long, sortKeyCol As Long, sortAscending As Boolean
    Dim a, s As Long
    startRow = 6
    areaCols = 5
    areaRows = 3
    sortKeyCol = 4
'    If MsgBox("Do you want to sort grades ascending or descending?" & vbCrLf & vbCrLf & "Click [Yes] for ascending" & vbCrLf & "Click [No] for descending", vbYesNo, "Sort by?") = vbYes Then
'        sortAscending = True
'    Else
'        sortAscending = False
'    End If
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = startRow To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
            s = 0
            a = .Cells(i, 1).Resize(areaRows, areaCols).Value
            For j = i + areaRows + 1 To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
                If s = 0 Then
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < a(1, sortKeyCol) Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > a(1, sortKeyCol) Then s = j
                Else
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                End If
            Next
            If s > 0 Then
                .Cells(i, 1).Resize(areaRows, areaCols).Value = .Cells(s, 1).Resize(areaRows, areaCols).Value
                .Cells(s, 1).Resize(areaRows, areaCols).Value = a
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Hopefully I have followed the directions correctly. Still only sorting one direction largest to smallest. Thank you for pluggin away at this pest. Enjoy your weekend.

 

[My Aswer Is This]

I've been watching this thread and saying nothing lately. Fluff is better at this stuff then me I believe. The main thing is the way I see things are this. The first step is to have the userform popup. The second thing is to select one of the three buttons depending on what you want to do. And then the code in that button will run. Ascending data goes in ascending button and descending data goes in descending button. I'm not sure why you have all the if functions.

 

[DeezNuts]

The code and ifs sort by grouping 3 rows together. So it starts at line 6 and groups 7,8,9 skips 10 groups 11,12,13 skips 14 etc....and repeats down the page for the first 5 columns while sorting based on a value found in column 4 of every group. Its not just sort everything in the column its has to group the rows also. The code woks flawless and it works perfectly with the vbyesno but not with a userform button set

 

[My Aswer Is This]

I'm not sure I can help but I would like to see the code that you say works perfectly. The I'll see if I can help. I like trying to solve challenges.

 

[DeezNuts]

This is the code that works perfect but uses vbyesno for the popup to select the options. Was wanting to ditch that part for a userform with 3 buttons Ascending | Descending | Cancel

Sub SortAreas()
    Dim i As Long, j As Long, startRow As Long, areaRows As Long, areaCols As Long, sortKeyCol As Long, sortAscending As Boolean
    Dim a, s As Long
    startRow = 6
    areaCols = 5
    areaRows = 3
    sortKeyCol = 4
    If MsgBox("Do you want to sort grades ascending or descending?" & vbCrLf & vbCrLf & "Click [Yes] for ascending" & vbCrLf & "Click [No] for descending", vbYesNo, "Sort by?") = vbYes Then
        sortAscending = True
    Else
        sortAscending = False
    End If
    
    Application.ScreenUpdating = False
    With ActiveSheet
        For i = startRow To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
            s = 0
            a = .Cells(i, 1).Resize(areaRows, areaCols).Value
            For j = i + areaRows + 1 To .Cells(.Rows.Count, sortKeyCol).End(xlUp).Row Step areaRows + 1
                If s = 0 Then
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < a(1, sortKeyCol) Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > a(1, sortKeyCol) Then s = j
                Else
                    If sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value < .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                    If Not sortAscending Then If .Cells(j, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value > .Cells(s, 1).Resize(areaRows, areaCols)(1, sortKeyCol).Value Then s = j
                End If
            Next
            If s > 0 Then
                .Cells(i, 1).Resize(areaRows, areaCols).Value = .Cells(s, 1).Resize(areaRows, areaCols).Value
                .Cells(s, 1).Resize(areaRows, areaCols).Value = a
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I'm not able to figure out what the Descending portion of this script is trying to do. It does not just sort them descending it's got a lot of if statements I don't understand. I ran the script without the Userform and do not understand what's it doing.

 

[DeezNuts]

made a brief video if it working with the vbyesno buttons

How is should be working - YouTube

would have shared it with the video tags but they dont work

ignore the last bit about the zero brain fart should have been six or eight cant recall now

 

[Fluff]

Should have had a better look at your code, on the following line remove the portion in red

Dim i As Long, j As Long, startRow As Long, areaRows As Long, areaCols As Long, sortKeyCol As Long[COLOR=#ff0000], sortAscending As Boolean[/COLOR]

This value is currently being declared twice which is causing the problem