Replicate text boxes in VBA

[dakota727]

I am trying to take a text box (formatted to look like a Post-it note) and have a user enter some text in a cell and use VBA to duplicate the style of the first text box, but add the new text and slightly move the new text box. The user would create <20 new text boxes and be able to drag them around and rotate, etc.

I had it almost working but ran into problems trying to center the text in the active shape and ran into problems with not actively naming the shapes, etc.

Any suggestions or guidance would be appreciated.

 

[My Aswer Is This]

If I understand. You have already created a TextBox

And formatted the Textbox the way you want.

And your now wanting:

and have a user enter some text in a cell and use VBA to duplicate the style of the first text box

The user will enter the text in a cell to add text to the TextBox??

Or you want user to enter text in cells to format the Textbox.

Now that would take a lot of code. Like TextBox size color etc. etc.

Please explain more.​

 

[dakota727]

To better explain what I am trying to do is make an Affinity diagram in excel, It is basically a bunch of post it notes that you can drag around the worksheet, rotate, group together, etc.

I think I have it figured out in the following way. I decided to just use rectangles with the following code. cell D7 the user enters the text for the note.

Private Sub CommandButton1_Click()

Dim shp As Shapes
Dim NoteCount As Integer
'I use the NoteCount variable to move the notes slightly so they do not completely overlap
NoteCount = ActiveSheet.Shapes.Count
NoteCount = NoteCount
If Range("D7").Value = "" Then
MsgBox "Enter Text for your Post-It note...", vbRetryCancel + vbExclamation, "ENTER TEXT"
Exit Sub

Else
End If


Set shp = ActiveSheet.Shapes
With shp
With .AddShape(msoShapeRectangle, 50 + NoteCount * 5, 200 + NoteCount * 5, 100, 100)
.TextFrame.Characters.Text = Range("D7").Value
.Fill.ForeColor.RGB = vbYellow
.TextFrame.Characters.Font.Color = vbBlack
End With
End With
Range("D7").Value = ""
Set shp = Nothing

End Sub

So far it looks to be working fine.

 

[My Aswer Is This]

Glad to see you have things working for you.
Take care.