H
Henk
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
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