InputBox to resize all charts of active worksheet

A

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
 
J

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
 
A

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
 

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