InputBox to resize all charts of active worksheet

[andreashermle]

Dear Experts:

below code sets a new width for all charts of the active sheet.

The input box functionality should be expanded with the following
features:

- the current width of the first chart is to be displayed as the
default width
- this default width is to be displayed in cm, not in points
- likewise the new width is to be entered in cm, not in points

Your professinal help is more than appreciated. Thank you very much in
advance.

Regards, Andreas


Sub ResizeCharts()

Dim myChtObj As ChartObject
Dim myInput As String

myInput = InputBox(Prompt:="New Width for all charts.", _
Title:="ENTER NEW WIDTH FOR ALL CHARTS", Default:="400")

For Each myChtObj In ActiveSheet.ChartObjects
myChtObj.Width = myInput
Next myChtObj

End Sub

 

[Javed]

Sub ResizeCharts()

Dim myChtObj As ChartObject
Dim myInput As String

'This conversion factor found in net(Not checked with other resources)
Const PoiToCm = 0.035277778

myInput = Application.InputBox(Prompt:="New Width for all charts.", _
Title:="ENTER NEW WIDTH FOR ALL CHARTS",
Default:=ActiveSheet.ChartObjects(1).Width * PoiToCm, Type:=1)

For Each myChtObj In ActiveSheet.ChartObjects
myChtObj.Width = myInput / PoiToCm
Next myChtObj


End Sub

 

[andreashermle]

Sub ResizeCharts()

Dim myChtObj As ChartObject
Dim myInput As String

'This conversion factor found in net(Not checked with other resources)
Const PoiToCm = 0.035277778

myInput = Application.InputBox(Prompt:="New Width for all charts.", _
          Title:="ENTER NEW WIDTH FOR ALL CHARTS",
Default:=ActiveSheet.ChartObjects(1).Width * PoiToCm, Type:=1)

For Each myChtObj In ActiveSheet.ChartObjects
    myChtObj.Width = myInput / PoiToCm
Next myChtObj

End Sub

Hi Javed,

great. This works just fine. Thank you very much for your great help.

Regards, Andreas