Check if a chart exists on the sheet before running macro.

abhay_547 · 11-02-2010, 03:01 PM

Hi All,

I have the below macro which loops through all sheets in a workbook and resizes the charts in the each sheet as per the size entered by user but It doesn't show a msgbox with text "that it didn't find any chart on a particular sheet". I want to put some code that it will show that it didn't find the charts in which all sheets at last the macro run gets completed.

Code to resize charts in multiple sheets:

Please Login or Register  to view this content.

Code to resize charts in activesheet:

Please Login or Register  to view this content.

I got the below codes so far to check the existence of charts on the activesheet sheet but I am not sure how to incorporate the same in the above macros.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks a lot for your help in advance.

**blane245** · 11-02-2010, 03:46 PM

Your question is unclear. If you are asking to display a message box containing all of the worksheets that do not include any charts, your "test" routine is almost there. The statement "If len(s) = 0" should read "if len(s) <> 0"

abhay_547 · 11-02-2010, 04:05 PM

Hi Blane45,

Thanks a lot for your reply, I still confused. Can you please replicate an example of the same on the below code as in the below code we are trying to resize charts on multiple sheets and I want my code to show a msgbox box after below macro completes which will show the sheets in which the macro didn't find the charts. for e.g. Msgbox "No Charts found in Sheet1 & vbnewline & Sheet2 & vbnewline Sheet4 etc.

Please Login or Register  to view this content.

Thanks a lot for your help in advance.

**blane245** · 11-03-2010, 07:54 AM

Sorry, I should have been more specific. Here is the code to resize all of the charts on all of the sheets and then display a mesage listing all of the sheets that do not contains charts.

Please Login or Register  to view this content.

You may want to change this so that things can be processed on an individual shhet at a time. Let me know if there is any thing else you want to do.

abhay_547 · 11-03-2010, 02:29 PM

Hi blane245,

Thanks a lot for your reply, I have tried running the modified code provided by you and it works fine as far as showing the msgbox with the sheetnames which doesn't have charts in them but the size which I enter the macro doesn't resize the charts according to the same. suppose I enter the Width as 400 in first input box and height as 200 in second input box but the when I check the size of the charts they are not same. for e.g. post running the macro when check the size it shows me that the width of the chart is 395 and height is 194. Below is the code which I use to check.

Please Login or Register  to view this content.

Thanks a lot for your help in advance.

**blane245** · 11-03-2010, 03:03 PM

I am not exactly sure how Excel sets chart width and height. When I do the same thing I get 399 and 199. It probably has something to do with the screen resolution - the conversion between points and pixels and back. Maybe someone else has more information on this topic. See this thread for more information.

abhay_547 · 11-03-2010, 03:58 PM

Hi blane245,

Thanks a lot for your reply, I was also trying to find the root cause of the issue but unfortunately I couldn't find anything so far. But I had one more issue which is very weird the resize charts macro code works fine at my end but at my office I have a file in which when I try to run the macro it doesn't work. .i.e. it resizes only the activechart on a sheet but rest of the charts remain as it is. It's very strange as it works in other workbook on my home pc. I have checked it from my end but I am not sure what is the issue. Can you please tell what can be the issue. I can't share my office data so that's why I can't upload the same file over here.

Thanks a lot for your help in advance.

**blane245** · 11-03-2010, 04:01 PM

In your original post you had macros that worked with all of the worksheets and macros that worked with the active worksheet. Are you sure you are running the right macro?