How do you make a macro dynamic?

[justanotheruser]

Hi guys,

I've got the following macro:

Sub UpdateDelivery()
Sheets("Del1").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("VAT").Shapes("TextBox 1").TextFrame.Characters.Text
Sheets("Del2").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("VAT & Disc").Shapes("TextBox 1").TextFrame.Characters.Text
Sheets("Del3").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("Zero VAT").Shapes("TextBox 1").TextFrame.Characters.Text
Sheets("Del4").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("Zero VAT & Disc").Shapes("TextBox 1").TextFrame.Characters.Text
End Sub

Which basically when executed, from the first line of code copies the contents of TextBox1 in the VAT sheet to TextBox 2 on the Del 1 sheet. This applies for the other 3 lines for the named sheets too. Currently, to run the macro this has to be done by executing it, but is it possible to make it work dynamically or perhaps not even need a macro at all if I can make TextBox 2 = TextBox 1 somehow? I've heard about something called Private Subs - but I don't know if that's what I need?

Thanks in advance!

 

[justanotheruser]

I don't understand: why do you want an end user to be able to change the font settings, etc? You, as the designer/developer of the workbook, change the text box properties (font etc.) at design time by clicking the Design Mode button on the worksheet containing the text box, then right-click the text box, then click Properties and change font, border colour etc.

The end users sometimes want to put more text in that has been allocated for the default size of the font, so they need to make the font smaller to fit it in - that's why I've let them edit the font size themselves.

Why are you using the Shapes collection? And isn't TextFrame to do with text boxes from the Drawing Toolbar? My example uses text boxes from the Control Toolbox, i.e. ActiveX controls which have user-defined events like TextBox1_Change which allow you to respond to user actions, exactly as shown in my example.

The Shapes collection was initially used because this is for an Invoice Maker that is print out and sent to customers, so it was originally designed it was very basic but to make it look slightly more attractive it was a Insert - Shape then right click on the shape and hit add text job - hence why shapes are used.

It might just be the time right now that I'm awake, but can you tell me what the properties are for emboldening text and making it larger through the properties of the ActiveX control - I couldn't find one which is why I wanted to go back to the Shape text box method!

Thanks again for your help so far.

 

[John_w]

The end users sometimes want to put more text in that has been allocated for the default size of the font, so they need to make the font smaller to fit it in - that's why I've let them edit the font size themselves.

Ah, I see. An ActiveX text box allows you to change the font size (and any other property) dynamically, without needing the user to edit the font size, like this:

Private Sub TextBox1_Change()
    If Len(TextBox1.Text) > 15 Then
        TextBox1.Font.Size = 8
    Else
        TextBox1.Font.Size = 10
    End If
End Sub

The above changes the font size to 8 if there are more than 15 characters in the box, otherwise a font size of 10 is used.

It might just be the time right now that I'm awake, but can you tell me what the properties are for emboldening text and making it larger through the properties of the ActiveX control - I couldn't find one which is why I wanted to go back to the Shape text box method!

It's the Font property. For your earlier question about border colour, change the BorderStyle and BorderColor properties, however there doesn't seem to be an option for a rounded border.