Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel - Returning Focus after calling a macro

Posted on 2003-02-23
3
Medium Priority
?
318 Views
Last Modified: 2008-03-10
I have a simple macro (a sort table routine) that is called upon any change on the spreadshett. However once the macro is called, focus is transferred to those cells, and I would like to return focus to the cell that was amended. Any ideas ?

Regards,
Andy

The routine for checking the change is as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        table
End Sub

And the sort is as follows:

Sub table()
    Range("Q3:V9").Select
    Selection.Sort Key1:=Range("V4"), Order1:=xlDescending, Key2:=Range("U4") _
        , Order2:=xlDescending, Key3:=Range("S4"), Order3:=xlDescending, Header _
        :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
End Sub
0
Comment
Question by:Maniac3575
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 8002523
Hello Maiac3575,

you could try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As range
  set rng=target
  call table
  rng.activate
End Sub

or (but i'm not sure)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  call table
  target.activate
End Sub

HAGD:O)Bruintje
0
 

Author Comment

by:Maniac3575
ID: 8002530
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 call table
 target.activate
End Sub


Works for me, many thanks
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8002538
you're welcome, didn't know for sure if the sort would trigger the event....thanks for the points
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
I came across an unsolved Outlook issue and here is my solution.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question