Macro assistance - sort,autorun on change and chart

[pedro-egoli]

The following macro which I recorded sorts the 10 highest values in a range and then produces a chart.

Just trying to work out how to make this macro autorun whenever a change is made to any cell .

Existing macro is

Sub Macro1()
'
' Macro1 Macro
'
'


'
    Range("A1:A100").Select
    
    Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:A10").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A10"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    
  
    End With
End Sub

After searching found this suggestion

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("A1:A100")
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call yoursub
Application.EnableEvents = True
End Sub

However, not sure how to incorporate it or in fact if it would work.

Any assistance appreciated.

Pedro

 

[energman58]

Yes it would work

You must put the new code in the sheet object for the sheet you want it to operate in (Sheet 1?). You should be able to see that in the left pane of the VBA editor usually these appear above the module - if you click on that it will bring a new window up. Each sheet can only have one Worksheet_Change procedure but you can include code for doing different things depending on which cell changess by using IF statements to look at the valueof TARGET.

To do what you want you need to change the line

Call yoursub

to

Call Macro1

Also you need to make sure that the range you set "r" to is correct for cells you want to trigger your macro when they change

And it should all work

 

[pedro-egoli]

Thanks Energyman,
Not completely sure of what you mean.

When I right clicked on Tab (sheet1) I pasted original code in sheet,

I then went back to find the Microsoft Excel objects , and noticed there was no module and that the code was in Sheet 1 .
I therefore went back to tab and right clicked and pasted into area below original code.

Then went to worksheet and in sheet 1 cell A7 changed the amount and hoped the macro would run.
It did not so any assistance would be appreciated, bearing in mind my limited knowledge of macro speak.
Under headings General / Macros this is what shows

Sub Macro1()
'
' Macro1 Macro
'
'


'
    Range("A1:A100").Select
    
    Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:A10").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A10"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    
  
    End With
End Sub

Sub Macro1()
'
' Macro1 Macro
'
'


'
    Range("A1:A100").Select
    
    Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:A10").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A10"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    
  
    End With
End Sub

Pedo

 

[pedro-egoli]

Forget my last post.

I got it to work by inserting a module and pasting original code into that.

Then altered second code to read "Call Macro1" and pasted it into Sheet 1.

Thanks very much.

Pedro

 

[energman58]

Excellent!

Happy to help