Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Bakir Farooq   on Nov 22 In MS Office Category.

  
Question Answered By: Horia Ahmed   on Nov 22

My suggestion is - don't.

Two reasons:

(1) This is pretty advanced stuff. As you say, you don't know any VBA. This is
not a good place to start. Get into VBA in a more gentle fashion.

(2) Sorting based on a change  to a cell is error-prone. If the user actually
wants to change to cells, side by side, or two cells  one under the other, then
the sort  will happen after the first cell is changed, and the user will then
probably go on to change the wrong cell next. (I'm speaking from experience - I
implemented just such a sorting method, but withdrew it hastily when people
starting making mistakes or getting confused.)

What I tend to do is to put a button at the top of the sheet to re-sort on
command. This leaves it under user control, but makes it much easier than going
through the menus.

Just create a control-toolbox button, then double-click it while in design mode.
This will open up the VB editor, and create a "click" subroutine, and you can
type in a sort command. If you type:

Call Range("A1").Sort(

then the pop-up assistance will guide you through the parameters needed  to do
your sort. For instance, one from one of my sheets:

Call Range("A1").Sort(Key1:=Range("E1"), Key2:=Range("F1"), Key3:=Range("A1"),
Header:=xlYes)

does a sort on three columns  (E, F and A). They are all defaulted to ascending.
There is a header row.

As you can see, this is the same as you would find if you pulled up the sort
command from the Data menu.

And that's the only line you need inside your click subroutine.

Share: 

 

This Question has 2 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Need help auto-sorting a data table Or get search suggestion and latest updates.


Tagged: