Creating resizing macro - select chart as object?
Hi,
I'm trying to create a macro in Excel so that I can click onto a chart and resize it without having to select the "Select Objects" arrow in the drawing toolbar (this is for very PC illterate folk). I recorded the macro and got this text: Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' ActiveSheet.Shapes("Chart 1").Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.ShapeRange.Height = 178.5 Selection.ShapeRange.Width = 340.5 With Selection .Placement = xlMove .PrintObject = True End With End Sub However, it doesn't work and I have a sneaking suspicion that I need to get something in there that will select the chart as an object rather than a chart. Can anyone help at all? I'm (quite clearly) winging it with Visual Basic! Many thanks, ellinoz |
Creating resizing macro - select chart as object?
Hi,
How about this. Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' With ActiveSheet.ChartObjects("Chart 1") With .ShapeRange .LockAspectRatio = msoFalse .Width = .Width * 0.66 End With .Placement = xlMove .PrintObject = True End With End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ellinoz" wrote in message ... Hi, I'm trying to create a macro in Excel so that I can click onto a chart and resize it without having to select the "Select Objects" arrow in the drawing toolbar (this is for very PC illterate folk). I recorded the macro and got this text: Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' ActiveSheet.Shapes("Chart 1").Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.ShapeRange.Height = 178.5 Selection.ShapeRange.Width = 340.5 With Selection .Placement = xlMove .PrintObject = True End With End Sub However, it doesn't work and I have a sneaking suspicion that I need to get something in there that will select the chart as an object rather than a chart. Can anyone help at all? I'm (quite clearly) winging it with Visual Basic! Many thanks, ellinoz |
Creating resizing macro - select chart as object?
Hi Andy,
That's great thanks. But is there a way to make the changes to the selected chart rather than whichever chart happens to be Chart1 on a sheet? Sorry - my recorded macro was a bit misleading in that respect! Thanks again, El "Andy Pope" wrote: Hi, How about this. Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' With ActiveSheet.ChartObjects("Chart 1") With .ShapeRange .LockAspectRatio = msoFalse .Width = .Width * 0.66 End With .Placement = xlMove .PrintObject = True End With End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ellinoz" wrote in message ... Hi, I'm trying to create a macro in Excel so that I can click onto a chart and resize it without having to select the "Select Objects" arrow in the drawing toolbar (this is for very PC illterate folk). I recorded the macro and got this text: Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' ActiveSheet.Shapes("Chart 1").Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.ShapeRange.Height = 178.5 Selection.ShapeRange.Width = 340.5 With Selection .Placement = xlMove .PrintObject = True End With End Sub However, it doesn't work and I have a sneaking suspicion that I need to get something in there that will select the chart as an object rather than a chart. Can anyone help at all? I'm (quite clearly) winging it with Visual Basic! Many thanks, ellinoz |
Creating resizing macro - select chart as object?
Here is a more generic version of the routine.
Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' If ActiveChart Is Nothing Then Exit Sub ' With ActiveChart.Parent With .ShapeRange .LockAspectRatio = msoFalse .Width = .Width * 0.66 End With .Placement = xlMove .PrintObject = True End With End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ellinoz" wrote in message ... Hi Andy, That's great thanks. But is there a way to make the changes to the selected chart rather than whichever chart happens to be Chart1 on a sheet? Sorry - my recorded macro was a bit misleading in that respect! Thanks again, El "Andy Pope" wrote: Hi, How about this. Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' With ActiveSheet.ChartObjects("Chart 1") With .ShapeRange .LockAspectRatio = msoFalse .Width = .Width * 0.66 End With .Placement = xlMove .PrintObject = True End With End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ellinoz" wrote in message ... Hi, I'm trying to create a macro in Excel so that I can click onto a chart and resize it without having to select the "Select Objects" arrow in the drawing toolbar (this is for very PC illterate folk). I recorded the macro and got this text: Sub Chart_two_third_width() ' ' Chart_two_third_width Macro ' Macro recorded 03/04/2008 by IT ' ' ActiveSheet.Shapes("Chart 1").Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.ShapeRange.Height = 178.5 Selection.ShapeRange.Width = 340.5 With Selection .Placement = xlMove .PrintObject = True End With End Sub However, it doesn't work and I have a sneaking suspicion that I need to get something in there that will select the chart as an object rather than a chart. Can anyone help at all? I'm (quite clearly) winging it with Visual Basic! Many thanks, ellinoz |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com