• 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.

Re: Urgent - Please Help: Code in creating and moving shapes in excel

S

sarmiej

New Member
Hi! I am basically new to the vba code creation and my goal for today is to create a customized heat map( somehow different to the traditional heat map). Would appreciate if anyone can help me on what codes should I use to create and move the oval shapes in excel.

First, i need to create an oval shape in excel. The number of shapes I should create corresponds to the number of "risk names" I have . Example I have 10 risks names (Risk 1 - 10), I should have 10 oval shapes to represent them.

Next thing I need to do is to move the shapes into a particular cell. Example Risk 1 has a rating of significant1, the oval shape 1 should then move to cell G6, which is the cell representing the significant1 area.

The concept is basically easy (create and move). However, I'm confused on what codes to be used. I have attached here the excel file I'm working with for you to have a clearer view of my goal. I do hope someone can save me from this excel problem. Thank you.
 

Attachments

Sarmiej

Firstly, Welcome to the Chandoo.org Forums

Have you considered putting a Scatter chart in front of the Matrix
Then using Formula to work out the locations as per the attached
No VBA required
 

Attachments

Hi Hui,

Thank you for the warm welcome and thank you for the reply, really appreciate it. The scatter chart looks fine however, it won't identify the risk name (code) that i need to plot into the heat map. Besides, the dots were located at the each line making it confusing to which particular cell it is, (e.g. it touches 4 cells and needs more interpretation before coming up with the right interpretation). Would you have other samples, that I may refer to? Thanks a lot Hui! :)
 
The problem with your approach is that you only have 5 values for both Likelihood and Consequence
In your example items 4, 5, 6 8, 9 & 12 all have the same Likelihood and Consequence
I woudl suggest giving them a numerical ranking eg moderate would go from 2 to 2.99
then the chance of getting points on top of each other is less
 

Attachments

Hi Hui,

Would there be a chance to rename the dots in scatter chart as to the corresponding risk number (instead of blank only)? Also, I've noticed that the numerical ranking does not correctly represents the rating.
Example:
1. There's only one significant rating but the scatter chart has ample dots on it, same with Extreme 3.
2. The Risk 3 = (4.2, 3.2) should be on High1 and not on Extreme3 cell.
 
Hi Hui,

Thanks for this. It's almost near to what I am supposed to do. How can I possibly change the label "Cell Range" into Risk Names (column D) please. Thank you.
 
I'm Confused, It is already in Column D ?

In Excel 2013, Select the chart and then select the Labels, Edit the Label Range
In other versions of Excel you need to use some VBA
But I have changed it to Column C in the attached file
 

Attachments

HI there
Do you just want to get a code for creating shapes in excel?If that so,i'd like to suggest you using some fine excel processing tool which supports to do that directly instead of using a code.It's a little bit complicated to process excle files using a code.
 
You must log in or register to reply here.
Back
Top