Deselect / Deactivate a selected picture

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
 
J

Joel

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
 
H

Henk

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top