1

I'm wondering if anyone could help/Have any ideas.

I'm trying to create a little script that will run through a list of words, and create circles depending on the number of times they appear in the list. I have already got the list which contains two columns. One being the word and the second being the number of times it appears.

The code I've written works fine to create the circles but what I am struggling with is the positioning of them. I would like them to all be in a contained area, and also not overlapping. I was wondering if anyone had any ideas about how I could get it so they are all in a box, next to each other with no gaps in between them.

This is my current code:

Sub bubble_test()
Dim ws As Worksheet
Dim cell As Object
Dim sh As Variant
Dim a, b, w, h, tot As Integer
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Sheets("Sheet1")

For Each cell In ThisWorkbook.Sheets("String Analysis").Range("StringAnalysis[Count]")
    tot = tot + cell
Next cell
a = 1
b = 1
For Each cell In ThisWorkbook.Sheets("String Analysis").Range("StringAnalysis[Word]")
    w = (cell.Offset(0, 1) / tot) * 5000
    h = w
    Set sh = ws.Shapes.AddShape(msoShapeOval, a, b, w, h)

    sh.TextFrame.Characters.Text = cell

    a = a + w

Next cell
Application.ScreenUpdating = True
End Sub

Thanks in advance

Tom

Tom
  • 9,725
  • 3
  • 31
  • 48
  • At the moment I doubt it is to. It was more of a curiosity to see if it could be done. – Tom Aug 28 '14 at 16:51
  • That would be a thought. How would you control the actual placings of it though? I was thinking the boundary size didn't matter, just so that it was in a square shape at the end was my plan – Tom Aug 28 '14 at 16:56

0 Answers0