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?

 

[Fluff]

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?

One way is to add this to the Thisworkbook module

Option Explicit

Private Sub Workbook_Open()

    UserForm1.Show

End Sub

Alternatively point a button at

Sub UsrFrm1()

    UserForm1.Show

End Sub

I'm not entirely sure I understand the first part of your post but HTH.
In the userform code module place

Option Explicit

Private Sub AscendingBtn_Click()

    SortAscending = xlAscending
    Call SortSheet
    Unload Me

End Sub

Private Sub DescendingBtn_Click()

    SortAscending = xlDescending
    Call SortSheet
    Unload Me

End Sub

and then in a normal module enter you macro

Option Explicit

Public SortAscending As String

Sub SortSheet()

    Cells.Sort Key1:=Range("A2"), Order1:=[COLOR=#0000cd]SortAscending[/COLOR], Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

Rather than run your variable SortAscending as True or False, I've changed it to xlAscending or xlDescending, so it can be plugged straight into a sort.
Hope this makes sense

 

[DeezNuts]

I appreciate you taking the time to assist but I cant make heads or tails of this. I tried and here is what I got

Excel File

The original macro is in that file also. With form I made but cant connect to the macro. I also tried to add an arrow on the sheet to activate the macro but as I mentioned this is going way over my head.


1) would like to be able to activate the popup for the macro via a the sheet with the arrow or something similar
2) would like the popup to be centered in the sheet and look like this

3) when the Ascending or Descending is picked it works as it should with the macro no additional confirmation popups
4) the cancel is new but just want it to close the popup.


The code I posted above in the first post is for the popup in the macro and am trying to change how it looks is all. again thank you for trying to explain it and sorry its lost on me.

Maybe someone can take the file I posted and edit it for me please.

 

[DeezNuts]

Curious if this is something that is tough to do if so please let me know so I forget having this added.

 

[My Aswer Is This]

Did you make this pop-up as you call it? Or is this just a image you posted here. We call these UseForms not popups? Do you know how to make these?

 

[mikerickson]

How about no user form and code like this. A message box removes the need to fiddle with calling user forms and passing values from the uf back to the calling routine and ....

Select Case MsgBox("Yes, I want to sort ascending" & vbcr & "No, I want to sort descending", vbYesNoCancel)
    Case vbYes
        'sort ascending
    Case vbNo
        'sort descending
    Case Else
        Exit Sub
End Select

 

[DeezNuts]

Did you make this pop-up as you call it? Or is this just a image you posted here. We call these UseForms not popups? Do you know how to make these?

Yes, I did figure out how to made the userform box that pops up when I run it. Its in the file I attached if they can be attached when saved.

How about no user form and code like this. A message box removes the need to fiddle with calling user forms and passing values from the uf back to the calling routine and ....

Select Case MsgBox("Yes, I want to sort ascending" & vbcr & "No, I want to sort descending", vbYesNoCancel)
    Case vbYes
        'sort ascending
    Case vbNo
        'sort descending
    Case Else
        Exit Sub
End Select

Yes, this idea works as its what I currently have except it doesnt have the cancel. I was just hoping to make it a tad easier by labeling the buttons instead of reading and remembering the no and yes. Seems this is too complex I will be okay with how it is. Thank you

 

[My Aswer Is This]

Go back to your userform and double click on the descending button and put in the code you have for descending. And at the bottom of the code put the code "Me.Hide" So when you click on this button the code will run and then hide the userform . Do the same with the ascending button. In the Cancel button just put the code "Me.Hide". To get the userform to run make a module and put the code "Userform1.show"

 

[My Aswer Is This]

Your getting there. Each button gets it portion of the code, Do not put all three parts in the same button.

 

[Fluff]

If you follow these steps, we should get you there.
1) double click on Ascending & enter this code

    sortAscending = xlAscending
    Call SortAreas
    Unload Me

2) do the same with Descending & enter this

    sortAscending = xlDescending
    Call SortAreas
    Unload Me

3) double click Cancel & enter

Unload Me

4) In Module1 remove existing code & replace with

Option Explicit
Public sortAscending As String

Sub UsrFrmShow()

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

5) Remove your code from Sheet2
6)Right click the Arrow on your sheet select Assign Macro & then select SortAreas