• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to resize objects according to certain parameters

C

Chuckles12

New Member
Hi all,


First time poster, am super excited to have found my way to this site!


I have a question regarding a report I am trying to make more automated. I have a pivot table in excel 2007 which I update regularly with new data. I have a template in powerpoint which I want to link from excel to powerpoint.


I know how to do some basic getpivotdata to populate parts of the template on another sheet, which I have linked into powerpoint, but then I start to run into some issues.


The issue I am having is trying to figure out a code which will adjust object sizes in excel (arrows to be precise, in regards to their width) based on the values they are pointing to. Basically, this template is reporting profits for different categories per individual clients, so an example would be if this sector was producing negative profits or zero, the arrow size width pointing to it would be much larger. If it was producing 100k in profits, it would be more narrow, etc.


There are also a lot of clients, so I am trying to figure out then how to have a 2nd macro copy the image after a new set of client details has been inserted and copy it to a new slide in the same powerpoint show.. Looping through say 80 different clients / creating 80 new slides automatically.


So it's basically 2 things I am trying to accomplish here, and I have no idea if using VBA can help with the object resizing based on certain cell parameters is possible? I am sure there is a way to loop through all the clients and paste each template into a new slide in the same show.


I hope this is clear enough, any and all advice would be greatly appreciated!!


Regards,
 
Hi ,


Since a macro is involved , it would be nice if you could upload a sample workbook , where at least two slides need to be created.


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
Hi there,


Many thanks for the response! I will get this up at some point today after making some amendments to figures (going to change all the confidential data etc.)
 
Hey guys,


I recreated my account (forgot my password... was sent to other email addy which I don't have access too at the moment!!).


Here is a link to my dropbox files with the excel sheets / powerpoint slides.


https://www.dropbox.com/sh/9h66pbtzohqxwjw/Jb89FQL1ud


I believe the easier module to render out of the 2 above is certainly the looping copy paste one. Essentially, the module would look up each "client" name , in the pivot table in the first worksheet and past the appropriate information into the appropriate cells in the 3rd worksheet. It would then copy this template image and paste it into a new slide in the powerpoint presentation (while keeping the same master file slide format), then loop through the 2nd name, paste information to template, paste template to a new slide in the same powerpoint presentation and repeat until all the client names have been gone through.


It is easy for me to have each cell in the template do a getpivotdata function for the stats. Perhaps the macro should take the name of client (from pivot table), paste it in the center of the template (which will populate each cell then due to getpivotdata function in the appropriate cells), paste the template to a new slide, then take the 2nd name, do the process again, and so forth.


What do you guys think is the best way to do this?


I have NO idea about the 2nd part of my inquiry earlier, about having the macro also automatically adjust arrow sizes according to what the number is in each cell. (so arrow width adjusts according to the number in each cell). Which part should be tacked first do you guys think?


Any and all help / advice GREATLY appreciated! all numbers / client names have been transformed to fake numbers so no issues on confidentiality end! Please let me know if anything is unclear or have any questions?


- chuckles13
 
Hi, Chuckles*!

two2 accounts two days ago and another one today. If you want you could post their email addresses (fully or partially which let identify them) and I'll reset the passwords.


Regards!
 
Hi ,


Can you check your file here ?


https://www.dropbox.com/s/5dne5fw4yz9nr3t/Priority%20Clients.xlsm


I have added a sheet tab labelled Configuration ; I have made two entries for the two shapes Rectangle 9 and Rectangle 35 ; do you think this can be done for all the other variable shapes on the Template tab ? This will be a one-time exercise , but once it is done , then just changing the Client Name in the cell Configuration!$B$2 will change all the text in all the relevant shapes on the Template tab.


Putting as much of the variable portion in formulae on the worksheets , will make the coding simpler. The only point against this will be if the formulae impact on the recalculation time.


Changing the size of the arrows is quite straightforward ; the following statement :


Shapes(Shape_Name).Line.Weight =


can be used to increase or decrease the thickness of the arrows ; can you specify the data which should be used to arrive at this size , including the minimum size ( and the corresponding data value ) and the maximum size ( and the corresponding data value ) ?


Narayan
 
Hi Narayank991,


Many thanks for your help so far! I'll definitely have to pick your brain a bit after I study the end result !!! ex: I never knew about the CHAR function / that you could make a drop down cell like you did in Configuration!B2 =)


In regards to your question regarding width paramaters for the arrows, here they are below:


No arrow = 0 or -ve

1 point >0 - 10k

2 points 10k - 100k

3 points 100k - 250k

4 points 250k - 400k

5 points 400k - 550k

6 points 550k - 700k

7 points 700k - 850k

8 points 850k - 1m

9 points 1m - 1.2m

10 points 1.2m - 1.4m

11 points 1.4m - 1.6m

12 points 1.6m - 1.8m

13 points 1.6m - 2m

14 points 2m - 2.5m

15 points 2.5m - 3m

16 points 3m+


m denotes millions in this case. For the transitions, for example from point 12 to 13, anything equal to or below 1.8 mio would be point 12, anything above 1.8mio would be point 13..etc. Same for all of the transitions.


I am going to go through each of the boxes now / tomorrow morning as you suggested. Is there any particular naming pattern I should follow in your "Shape Name" Column? I will add them all appropriately. Also, I believe that I will have to do some groupings (for example, the variable box in the template "EM Flow" is technically 2 or 3 of the fields within Trading Group L4). This will create a new field, such as "Trading Group L4_2", or would it be easier to just state that "EM Flow" = sum(this + that + this + that), etc? Also, I believe Corp Fin. and some of the other boxes are constructed using Trading Group L3, etc. Is there any way in which I should lay out the pivot table so that your code will be able to capture it? I just don't want to your code to not work because I failed to mention this!


Just to follow up, will the macro work by going through each name in the pivot table, inserting it into cell Configuration@B2 , then copying/pasting the template with the newly populated figures into a new powerpoint slide in the ppt file, then taking the next client name, copy/pasting into new slide, and repeating until all then names have been done? I just want to understand the methodology behind it or if you were going a completely different route! Very interesting to me I admit.


I really appreciate all of your assistance, I definitely didn't think this was possible before hand but now can see that there is certainly light at the end of the tunnel!!


Best regards,


chuckles
 
heya, think I answered my own pivot table question (at least part of it) : obtaining the value's isn't an issue if I also add in trading group L2 / L3 into the columns - they are still all obtained correctly.


Not sure about the ones in which multiple need to be either grouped / added however.


- Chuckles
 
Hi Chuckles ,


There is no need to rename any of the shapes you have already created in your Template tab ; just put down their names in the Configuration tab ; all that is needed is a linkage between the shape names , their related arrows , and the text that is supposed to be displayed in them.


If the text within each shape is going to depend on the trading group , then it will be better to introduce a column within the configuration data range , where this is specified , so that the appropriate text can be retrieved from the DATA tab.


Your understanding of how the macro will work is the same as what I had thought of ; we need to see if it will work !


Once you have entered all the data on the Configuration tab , please upload your workbook ; I'll see if I can do whatever you posted in your first post.


Narayan
 
ah alrighty - will be working on this throughout the day.


Will upload the workbook later tonight. Many many thanks again for your insight.


Chuckles
 
Hi Narayan,


I have completed the majority of the variable boxes. I have placed all of the pivot fields into the table and they are pulling the stats accordingly.


I just do not know how to adjust the formula correct to pull the stats from the table for the following variable boxes:


Non EUR Swaps

EUR Swaps

Corp Fin

EMC Other

Commodities

Other


Below says which Trading Group to populate each box from:


Trading Group L2:


- Corporate Finance

- Other = (Commerz Real + CPM + Global Liquidity & Risk Mgt + Grosskunden + Treasury CEE + Treasury HF + Unknown business group)


Trading Group L3:

- Commodities

- EMC Other = (EVF + Securities Finance)


Trading Team:

- EUR swaps

- Non-EUR swaps


I am currently at the office, which does not allow me access to file sharing sites =( Either I will have to wait until I get back home to post my file, or perhaps I can email it to you? Just let me know


- Chuckles
 
You must log in or register to reply here.
Back
Top