Excel VBA UserForms #2

Create a UserForm with 2 option buttons:  1 Sort by Employee ID or 2 Sort by Name.

Part one, create the UserForm and give it buttons.

  1. First, bring up the VBA UserForm editor (F11), and add a new UserForm  using the dropdown in the upper left of the screen.UserForm Dropdown
  2. Rename the UserForm to “sortform” and change the caption to “Choose sort option”:Screen Shot 02-12-16 at 02.51 AM
  3. Add two Option Buttons (or as many as you need for your form), and two Command Buttons to your UserForm using the toolbox:Screen Shot 02-12-16 at 02.57 AM
  4. Next, (name) and Caption your command buttons (sort and cancel, respectively).
  5. Then let’s program the Cancel button, using the .hide command from UserForms #1 video.Screen Shot 02-20-16 at 09.13 PM
  6. Now we’ll edit the caption on the Option buttons, naming one Emp Id, and the other Name.

Part Two, recording a macro

  1. Click the Record macro button, hit ok on the window that pops up, and select your range of cells you wish to include for sortingCell range selection
  2. Select Data on the menu bar and hit the sort button, then select Emp Id in the Sort by dropdown box and hit ok. Repeat for the Name column.Sort by selection box
  3. Bring up the VBA window with alt-F11

 

Part three, use the macro to code the buttons

  1. Go to the most recent module and copy the selection as shownCode Selection
  2. Select the sortform and double-click the sort button to paste in the codeCode insert
  3. Add in the following code above your pasted code:
    Set sdsheet = ThiseWorkbook.Sheets (“SORT db”)
    
    
    If sdsheet.Cells(Rows.Count, 1) .End(x1Up) .Row = 1 Then
        lr = 2
    Else
    	lr =  sdsheet.Cells(Rows.Count, 1) .End(x1Up) .Row
    End If
    
    Set SortA = sdsheet.Range (“a2:g” & lr)
  4. Add in this code, or for step-by-step instructions and complete explanation, please watch the full video below!
    If Me.obEmp = True Then
        'SORT BY EMP
    	sdsheet.Sort.SortFields.Clear
    	sdsheet.Sort.SortFields.Add Key:=sdsheet.Range(“a2:a” & lr) _
    		, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:xlSortNormal
    	With sdsheet.Sort
    		.SetRange SortA
    		.Header = xlNo
    		.MatchCase = False
    		.Orientation = xlTopToBottom
    		.SortMethod = xlPinYin
    		.Apply
    	End With
    ElseIf Me.obName = True Then
    	'SORT BY NAME
    	sdsheet.Sort.SortFields.Clear
    	sdsheet.Sort.SortFields.Add Key:=sdsheet.Range(“b2:b” & lr) _
    		, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:xlSortNormal
    	With sdsheet.Sort
    		.SetRange SortA
    		.Header = xlNo
    		.MatchCase = False
    		.Orientation = xlTopToBottom
    		.SortMethod = xlPinYin
    		.Apply
    	End With
    End If
    
    End Sub

 

Get the FREE eBook. . .
Signup today and receive free updates straight in your inbox plus we'll send you the "Essential Excel VBA Snippets" eBook. We will never share or sell your email address.