Userform help needed to replace current one with a newer one

DeezNuts

Board Regular
Joined
Aug 12, 2014
Messages
177
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

Code:
    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
LtilKM2.png


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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
Code:
Option Explicit

Private Sub Workbook_Open()

    UserForm1.Show

End Sub
Alternatively point a button at
Code:
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
Code:
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
Code:
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
 
Upvote 0
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

cZ45HdZ.png


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.
 
Last edited:
Upvote 0
Curious if this is something that is tough to do if so please let me know so I forget having this added.
 
Upvote 0
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?
 
Upvote 0
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 ....
Code:
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
 
Upvote 0
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 ....
Code:
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
 
Last edited:
Upvote 0
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"
 
Upvote 0
Your getting there. Each button gets it portion of the code, Do not put all three parts in the same button.
 
Upvote 0
If you follow these steps, we should get you there.
1) double click on Ascending & enter this code
Code:
    sortAscending = xlAscending
    Call SortAreas
    Unload Me
2) do the same with Descending & enter this
Code:
    sortAscending = xlDescending
    Call SortAreas
    Unload Me
3) double click Cancel & enter
Code:
Unload Me
4) In Module1 remove existing code & replace with
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,286
Messages
6,171,185
Members
452,390
Latest member
kemafa77

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