Joel,
Thanks for your answer.
For line : Chrt.Paste
I got message : Run-time error '438': Object does not support this property
or method.
I changed that to
ActiveChart.Paste
Everything 's working fine now.
Many tHenks
"Joel" wrote:
> try these changes
>
> Private Sub Worksheet_Change(ByVal Target as Range)
>
> If ActiveCell.Address = "$E$5" then ' Country choice is in Cell E5
> Application.ScreenUpdating = False
> Dim CountryOld as String
> Dim CountryNew as String
> Dim FlagOld as String
> Dim FlagNew as String
> CountryOld = "_" & AtiveSheet.Range("Graph01_Last_country_abb").Value
> CountryNew = "_" & ActiveSheet.Range("Graph01_New_country_abb").Value
> FlagOld="Flag_" & AtiveSheet.Range("Graph01_Last_country").Value
> FlagNew="Flag_" & AtiveSheet.Range("Graph01_New_country").Value
>
>
> Set NewFlg = Sheets("Data availability").Shapes(FlagNew)
>
> 'Retrieving data for new country
> Range("Graph01_Data").Replace What:=CountryOld, Replacement:=CountryNew,
> Lookat:=xlPart
> Range("Graph01_Last_country").value = Range("Graph01_Country_choice").Value
>
> Set Chrt = ActiveSheet.ChartObjects("Graph01")
> Chrt.Activate
> Chrt.Shapes(FlagOld).Delete
>
> NewFlg.Copy
> Chrt.Paste
>
> ActiveWindow.Visible = False
> Sheets("Data availability").Select
> Sheets("Graph01").Select
>
> End if
>
> End Sub
>
> "Henk" wrote:
>
> > I have a chart in sheet "Graph01" and some flags (pictures) in sheet "Data
> > availability". Based on a choice list result (countries) I want to delete the
> > "old" flag from the chart and copy the new one in.
> >
> > Everything works fine, except ................. once the macro has run, the
> > moment I press an arrow key Excel stops working, starts trying to recover my
> > information (for several minutes).
> >
> > I have tried everything the last 36 hours, but nothing worked so far. I do
> > know that undoing the selection of the flag on sheet "Data availability" does
> > help. I then can use my arrow keys after the macro ran. But I have not found
> > a way to establish that within my macro.
> >
> > Application.CutCopyMode = False
> > did not help
> >
> > Worksheets("Data availablity").Select
> > Range("A1").Select
> > Did not help (it even results in the error : Run-time error '1004': Select
> > method of range class failed. Perhaps this is some indication??)
> >
> > The complete code (most of it recorded by Excel itself) of this moment is :
> >
> > Private Sub Worksheet_Change(ByVal Target as Range)
> >
> > If ActiveCell.Address = "$E$5" then ' Country choice is in Cell E5
> > Application.ScreenUpdating = False
> > Dim CountryOld as String
> > Dim CountryNew as String
> > Dim FlagOld as String
> > Dim FlagNew as String
> > CountryOld = "_" & AtiveSheet.Range("Graph01_Last_country_abb").Value
> > CountryNew = "_" & ActiveSheet.Range("Graph01_New_country_abb").Value
> > FlagOld="Flag_" & AtiveSheet.Range("Graph01_Last_country").Value
> > FlagNew="Flag_" & AtiveSheet.Range("Graph01_New_country").Value
> >
> > 'Retrieving data for new country
> > Range("Graph01_Data").Select
> > Selection.Replace What:=CountryOld, Replacement:=CountryNew, Lookat:=xlPart
> > Range("Graph01_Last_country").value = Range("Graph01_Country_choice").Value
> >
> > ActiveSheet.ChartObjects("Graph01").Activate
> > ActiveChart.Shapes(FlagOld).Select
> > Selection.Delete
> > ActiveWindow.Visible = False
> > Windows("Name of the workbook").Activate
> > Sheets("Data availability").Select
> > ActiveSheet.Shapes(FlagNew).Select
> > Selection.Copy
> > Sheets("Graph01").Select
> > ActiveSheet.ChartObjects("Graph01").Activate
> > ActiveSheet.ChartArea.Select
> > ActiveChart.Paste
> > ActiveWindow.Visible = False
> > Windows("Name of the workbook").Activate
> > Range("$E$5").Select
> >
> > End if
> >
> > End Sub
> >
> > Many thanks for your help.
> >
> > Regards,
> >
> > Henk
> >
> >
> >
> >
> >
|
Similar Threads
