Creating several TextBoxes (Shapes) with cell references as contents

A

andreashermle

Dear Experts:

below macro grabs the contents of cell B7 and displays it in a text
box (shape, not a control!). The formula is a cell reference so that
the text box contents is updated whenever the respective cell changes
its contents.

I would like to have the macro expanded so that several input boxes
are created with the following requirements:

1. An input box prompts the user to enter a number: this number
represents the iterations / number of text boxes to be created, say
the user has entered the number 5

2. another input box prompts the user to enter the first cell
reference, say B7.

Now the macro should do the following:

1. Iterate thru the macro 5 times
2. The formula part is to change as follows with each loop: the row
number of the cell reference is incremented by 1 with each loop, ie.
B7, B8, B9, B10, B11.
3. All in all: 5 text boxes are to be created with their respective
cell references

Hope this does not exceed the scope of this forum.

Help is much appreciated. Thank you very much in advance. Regards,
Andreas


Sub AddTextBox()
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddTextBox( _
msoTextOrientationHorizontal, 97.5, 28.5, 96.75, 17.25)
shp.DrawingObject.Formula = "B7"
With shp.TextFrame.Characters.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top