Can i run VB code when a user changes Worksheets within an Excel Workbook

  • Thread starter Thread starter Douglas
  • Start date Start date
D

Douglas

I have a Excel Spreadsheet with 6 worksheets in it

The first sheet contains all the data and the other sheets do various
things with the data (graphs etc)

On the first sheet i have buttons above some of the table colums which
sort the data by that colums. The buttons run VB code

Now what i would be able to accomplish is if a user clicks on one of
the other sheets, firstly I want to sort the data in Sheet 1 by the
first column to get the data back in the correct order, otherwise the
graphs are all wrong on the other sheets

I need some kind of way to enable the following code to run when the
user clicks on another sheet, like some kind of Exit Routine in Sheet
1


My code to sort by the first column is
Private Sub CommandButton1_Click()

Range("B3:P72").Select
Range("P72").Activate
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 3
Range("A1").Select

End Sub

Is this possible

TIA

Doug
 
Hi Douglas

there is a worksheet_deactivate event - which is the one i think you want -
right mouse click on sheet1's tab, choose view code, on the left hand drop
down choose "worksheet" - on the right choose "deactivate" - so just copy
and paste your code in here - then edit your code as follows:

Private Sub Worksheet_Deactivate()

Sheets("Sheet1").Range("B3:P72").Sort Key1:=Range("B3"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


Hope this helps
Cheers
JulieD
 
Back
Top