|
Deselect / Deactivate a selected picture
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 |
Deselect / Deactivate a selected picture
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 |
Deselect / Deactivate a selected picture
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 |
| All times are GMT +1. The time now is 04:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com