VB application to sort a spreadsheet

[rabbo]

Hello,

I wrote a quick subroutine that sorts a spreadsheet based on the value in that row. It hides all the irrelevant rows.. I didn't know how to really do this, so it hides all the rows and then unhides the ones I want. See below:

Dim rg As Range
Dim rg_not As Range
Dim rg_tot As Range
Dim rg_ind As Range
Set rg_ind = Range("a10", "f20")
rg_ind.EntireRow.Hidden = True
With Sheets(1).Range("c10:e16")
Set rg = .Find(my_x)
If rg_tot Is Nothing Then
Set rg_tot = rg
End If
If Not rg Is Nothing Then
Set rg_not = rg
Do
Set rg_tot = Union(rg_tot, rg)
Set rg = .FindNext(rg)
Loop While Not rg Is Nothing And rg.Address <> rg_not.Address
End If
End With
If rg_tot Is Nothing Then GoTo rd_undo
rg_tot.EntireRow.Hidden = False
End
rd_undo:
rg_ind.EntireRow.Hidden = False

End Sub


My question is this: I now want to create a dropdown menu in Excel that will have all the possible values to sort by (essentially building my own Autofilter). How can I set the sub so that it updates every time the value in the cell changes (i.e., user re-sorts)? If not, I suppose I could put the macro on a button, anyone know if that is better?

Thanks ahead of time,

rabbo