VBA Noob - Sort Sheet on Worksheet Change

[phatty70]

Hello Excel Guru's

Been lurking for a while, greatfull to all on this site that take the time to post solutions and help for those less....fortunate .

Quick question. I have a sheet that is reference for a few vlookup functions. Users add data to this sheet and always neglect to sort. I'd like to add some VBA to sort the sheet when they leave (I believe it's the "Worksheet_Change" Function.

Setup
Sheet 1 - Where vlookup functions reside
Sheet 2 - Data that needs sorted

• range dynamic if possible starting with cell b2 getting all data on sheet (row 1, headers).
• Sort by column a

Any help is GREATLY appreciated.

Thanks in advance - John

[mikerickson]

A couple of things:
1) If you set the last argument of VLOOKUP to False, then no sorting is needed. But, that requires an exact match.

2) The Worksheet_Change event fires whenever a cell in the worksheet is changed. Tying a sort to this can be very confusing; type something into a cell, press Enter and HEY PRESTO! the sheet has been rearranged and you have to go hunting for the cell you were working on.

You might want to record a macro of you sorting the sheet and link it to a less volatile event, like Worksheet_Deactivate or Worksheet_Activate.

[mrice]

I would suggest the Sheet_deactivate event to avoid data sorting interactively.

Please Login or Register  to view this content.

Paste the above into the Sheet2 tab in the VBA editor.

[phatty70]

Thanks a bunch, just what the doctor ordered

[mikerickson]

I find it convenient to have a Double Click event that sorts a sheet on the column being double clicked.

Please Login or Register  to view this content.