Need code to make circle within VB and place in Excel cell with number in middle of circle

I have an Excel worksheet that lists the risks on our program.  I am developing a risk matrix within another Excel worksheet to depict the ranking of the risks.  I want to place a circle within certain cells that depict the ranking of the risk (occurance/impact) along with the number of the risk embedded in the circle.  Is it possible to do that?  Or do I need to put the number beside the circle?  If so, could you provide example code that will do that?
chaticatheAsked:
Who is Participating?
 
StephenJRCommented:
I had a play and came up with this. It will accommodate 9 ovals in any square, but I guess could be adjusted:
Sub Macro2()

Dim r As Range, s As Shape, r1 As Range, v(1 To 5, 1 To 5), x As Double, y As Double

For Each r In Sheet1.Range("C2", Sheet1.Range("C" & Rows.Count).End(xlUp))
    If Not IsEmpty(r) Then
        v(r.Value, r.Offset(, 1).Value) = v(r.Value, r.Offset(, 1).Value) + 1
        Set r1 = Sheet4.Range("B24").Offset(-4 * (r.Offset(, 1)), r * 2 - 1)
        Select Case v(r.Value, r.Offset(, 1).Value)
            Case 1 To 3: y = 0
            Case 4 To 6: y = 1
            Case 7 To 9: y = 2
        End Select
        Select Case v(r.Value, r.Offset(, 1).Value)
            Case 1, 4, 7: x = 0
            Case 2, 5, 8: x = 1
            Case 3, 6, 9: x = 2
        End Select
        Set s = Sheet4.Shapes.AddShape(msoShapeOval, r1.Left + x * 15, r1.Top + y * 15, 25, 15)
        s.TextFrame.Characters.Text = Val(Replace(r.Offset(, -2), "R-", ""))
        s.TextFrame.Characters.Font.Size = 8
    End If
Next r

End Sub

Open in new window

0
 
StephenJRCommented:
Could you post a sample workbook? Can't quite visualise what you want to do. You can certainly put text inside a circle.
0
 
chaticatheAuthor Commented:
The risk matrix is in a separate worksheet.  I want to draw a circle in the appropriate cell in the risk matrix according to the ranking of the risk (likelihood and consequence values).  I also want to extract the number from the risk number and put inside the circle (1,2,3,4).  
Risk-Matrix.pdf
Risks.pdf
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
StephenJRCommented:
Can you post an Excel workbook?
0
 
chaticatheAuthor Commented:
Yes.  Please see attached.  
RIO-Mgmt-test.xlsm
0
 
StephenJRCommented:
What I was after was some indication of the results you were envisaging. Can you add that?
0
 
chaticatheAuthor Commented:
Yes, I apologize, please see attached.
RIO-Mgmt-test.xlsm
0
 
StephenJRCommented:
I see now. Presumably if you have more than one entry in a square you want the ovals regularly spaced?
0
 
StephenJRCommented:
To add to this, how many are likely to be in any square - is there a theoretical or practical limit?
0
 
chaticatheAuthor Commented:
This is EXACTLY what I was looking for.  Thank you so much...you have saved me alot of time!!  I appreciate it more than you know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.