Alphabetizing Function in Excel 2003?

Question

I've recently put my movie library onto an Excel 2003 sheet, now what I'm wondering are two things; the first being that can I put in a function that will automatically alphabetize the column, and secondly, when I put a check mark on the discs that I've made backups of, can I have that check mark 'stick' to the original movie title (when I add a new movie, then alphabetize them using the button at the top, the check mark stays at the position it once was at, not linked to the movie that was made as a back up)?

garbo7441 · Accepted Answer

Your Sort routine needs to include both columns, the title and the check mark column, if you wish data integrity to be maintained.

Assuming your data is in columns A and B, with labels in the first row (Title & Backed Up for example), you can use the following event handler to automatically sort on double clicking any cell.

If your columns are not A:B, modify the code before copying.  If your columns are D:E, change the "A:B" reference to "D:E".  Change the "A2" reference to "D2".  The "A1" reference is simply the cell that is selected at the end of the code run.

Copy the following event handler to the clipboard (highlight the entire code, right click in the highlighted area, and 'Copy')

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Application.ScreenUpdating = False
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
Orientation:=xlTopToBottom, dataOption1:=xlSortNormal
Range("A1").Select
End Sub

Select the worksheet containing the data and right click the sheet tab.

Select 'View Code'

Paste the event handler into the white editing area to the right (right click in the area, and 'Paste').

Close the VBE (red button - white 'x')

To sort the data, simply double click any cell in the worksheet.  When new data is added, just double click again.  No need for a command button.

================

Edit:  If you are, indeed, using inserted checkbox controls on your worksheet, they will not sort with the cell data.  If you really want to show a 'checkmark' in the column, you can do that using a font/letter combination with a SelectionChange event handler.

For example, this will alternately place and remove an actual check mark in column B whenever you click any cell in column B.  If you wish to use a different column, say 'E' change both column reference values '2' to 5 (E).  For other columns, use the equivalent number:  A = 1, Z =26, etc.

Copy this event handler to the clipboard:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Target.Value = "" Then
ActiveCell.Font.Name = "wingdings 2"
ActiveCell.Font.Size = 12
ActiveCell.Font.Bold = True
ActiveCell.Value = "P"
ActiveCell.HorizontalAlignment = xlCenter
ElseIf Target.Column = 2 And Target.Value <> "" Then
Target.Value = ""
End If
End Sub

Select the worksheet containing the data and right click the sheet tab.

Select 'View Code'.

Paste the event handler into the white editing area to the right.

Close the VBE and return to the worksheet.

Select any cell in column B and a check mark will be entered in the cell.  Exit the cell, the click on it again and the check mark will be removed.  Kind of a 'toggle' thing.

Scrawny · Answer

Put the check mark or whatever mark you are identifying as backed up in a separate column. When alphabetizing your list, select all the data then on the Home Tab, Sort and Filter, Custom sort. Select the column with the movie titles to sort on and all the check marks will be moved with the movies that they are associated with.

I have a similar listing and it works great.

The sort is remembered and the next time that you want to sort the list, select all the data and click on the Sort and filter button and the sort will be done without you having to re-construct the sort that you want.

Trending News

Alphabetizing Function in Excel 2003?

2 Answers