Prev: Select one cell insteade of a range
Next: variable target address
From: andreashermle on 17 Dec 2009 11:48
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


 | 
Pages: 1
Prev: Select one cell insteade of a range
Next: variable target address