Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Worksheet_Change Question

Status
Not open for further replies.
tweek312

tweek312

Technical User
Dec 18, 2004
148
US
I have a set of about 10 worksheets in a workbook which are setup like logs. They have a spot where a work order is entered and then is totaled on a mainpage where data for all sheets is visible in a report like format. I need to be able to sort a set of values on one worksheet each time a new work order is added. This is done to keep a bar graph in order from highest to lowest, esentially reorganizing the graph each time a work order is added. I am using the Worksheet_Change event as the trigger but am having a tough time getting the change to trigger when changes are made on sheets other than the one with the code.

I need some specifics on which sheet to put the code into. Weather it be all of them, the workbook, or even if worksheet_change is what I should be using for this task.

I have tried everything I can think of so any help is good help.

Thanks,
tW33k

Code: 
Option Explicit
Sub Worksheet_Change(ByVal Target As Range)
    
    Sheets("live_source").Select
    Range("F41:G49").Select
    Selection.Sort Key1:=Range("G41"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
End Sub
 
Sort by date Sort by votes
Hi tweek,

The WorkSheet Change Event is for a single worksheet only. If you want the same code to trigger for all worksheets, use the WorkBook SheetChange Event.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
 
Upvote 0 Downvote
And don't forget to play with the Application.EnableEvents property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote


Also keep in mind that if you use the Workbook_SheetChange event, AND there are OTHER SHEET(s) that do NOT use this code...

then use the Sh Object to specify which sheet(s) do/do not apply the logic.

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
I am putting the code In the ThisWorkbook module and I am receiving the error below.

error1.gif


Code: 
Option Explicit
Sub Workbook_SheetChange(ByVal Target As Range)
    
    Sheet("live_source").Select
    Range("F41:G49").Select
    Selection.Sort Key1:=Range("G41"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
End Sub
 
Upvote 0 Downvote
You may try this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents False
With Sheet("live_source")
.Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Application.EnableEvents True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
This code does not seem to work. Why do we need the "Application.EnableEvents = False/True"?

Reason I ask is because it kinda messed up my sheets for a little bit...

Please Please Help!
I thought this would be an easy one for you guys.

tx.
tW33k
 
Upvote 0 Downvote

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents False
'if you have a table to sort on EVERY SHEET then use the [b]Sh[/b] object -- otherwise you need to discriminate to sort only the sheets that have such a table to sort.
    With [b]Sh[/b]
        .Range("F41").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
            , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
    Application.EnableEvents True
End Sub


Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
Using the code as is I am receiving this error:
vberror.gif

When going back into the editor the lines below are highlighted:

Code: 
.Range("F41").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
            , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
 
Upvote 0 Downvote
The area on the single worksheet (Sheet2) or "live_source" as it is named is circled in the picture.

wksht2.gif
 
Upvote 0 Downvote
And this ?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sheet("live_source")
If .Name = Sh.Name Then Exit Sub
Application.EnableEvents False
.Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents True
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote


In my version of Excel, I get clear the error is I do not include
Code: 
Sub Worksheet_Change(ByVal Target As Range)
    
    Sheets("live_source").Select
    Range("F41:G49").Select
    Selection.Sort Key1:=Range("G41"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False[b][s], Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal[/s][/b]
    
End Sub


Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote


This actually works...
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
'if you have a table to sort on EVERY SHEET then use the Sh object -- otherwise you need to discriminate to sort only the sheets that have such a table to sort.
    With Sh
        .Range("F41").CurrentRegion.Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlNo _
            , OrderCustom:=1, MatchCase:=False
    End With
    Application.EnableEvents = True
End Sub

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
The below code (skips) works when making changes to the target sheet only. Input on all other sheets will result in the error which I had last posted.

How do I descriminate to sort just Sheet2. I thought replacing Sh with Sheet2 would do the trick but that seemed to fail.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
'if you have a table to sort on EVERY SHEET then use the Sh object -- otherwise you need to discriminate to sort only the sheets that have such a table to sort.
    With Sh
        .Range("F41").CurrentRegion.Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlNo _
            , OrderCustom:=1, MatchCase:=False
    End With
    Application.EnableEvents = True
End Sub
 
Upvote 0 Downvote

Sh is the ActiveSheet.

That means that if you make a change on sheetwhatever and there is no table in F41 and column of data to sort in G41, it will error.

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
I understand but how do I tell it to only sort the data on sheet2 and ignore all of the others?

Tx.
tW33k
 
Upvote 0 Downvote
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sheet("sheet2")
Application.EnableEvents False
.Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False
Application.EnableEvents True
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote


If you only want to sort when Sheet2 is the ActiveSheet...
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
  if .Name = "sheet2" then
    Application.EnableEvents False
    .Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
      , OrderCustom:=1, MatchCase:=False
    Application.EnableEvents True
  end if
End With
End Sub
if you want to sort sheet2 ANY TIME a change is made on ANY SHEET then
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sheet("sheet2")
  Application.EnableEvents False
  .Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False
  Application.EnableEvents True
End With
End Sub


Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
None of them worked right off the bat but, after fiddling a bit I got the following to work exactly as I wish.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sheet2
  Application.EnableEvents = False
  .Range("F41:G49").Sort Key1:=.Range("G41"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False
  Application.EnableEvents = True
End With
End Sub

Thanks Guyz, Ur all great!

tW33k
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Using Err.Raise question
Replies
2
Views
1K
mintjulep
M
Shaunfish
  • Locked
  • Question
Word combobox question
Replies
6
Views
476
strongm
strongm
remeng
  • Locked
  • Question
.CustomDocumentProperties Save Only question
Replies
8
Views
411
remeng
remeng
simian336
  • Locked
  • Question
DoCmd.ApplyFilter Question
Replies
2
Views
284
SkipVought
S
remeng
  • Locked
  • Question
Query .AfterRefresh questions
Replies
6
Views
826
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top