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 clicked on TextBox1 in the VAT sheet and defined a name for it - called it VATTextBox and it refers to <CODE>="TextBox 1"</CODE>. However, if I go to TextBox 2 in the Del1 sheet and go to the formula bar and type =VATTextBox, I get "Reference is not valid" error - the scope of the VATTextBox is the entire workbook.

 

[justanotheruser]

If I put <CODE>=VATTextBox</CODE> into a cell, it prints out TextBox 1 - so how do I define the name to refer to the actual shape, instead of the name of the shape? Thanks again!

 

[justanotheruser]

I've done some googling, I can't find what formula you can use in a shape to make it equal another shape - so if anyone could help make the code in my first post work dynamically, I'd really appreciate it!

 

[John_w]

If you use textboxes from the Control Toolbox (i.e. ActiveX textboxes), you can make TextBox1 on Sheet1 automatically change to the contents of TextBox1 on Sheet2 by putting the following code in the Sheet2 module:

Private Sub TextBox1_Change()
    Sheets("Sheet1").TextBox1.Text = TextBox1.Text
End Sub

See if you can adapt this for your situation.

 

[justanotheruser]

Thank you for that code John, works perfectly! Is there any way I can reshape the active x text box to make it a rounded edge rectangle with a black border?

 

[John_w]

Maybe - right-click the text box and play around with its Properties.

 

[justanotheruser]

Yep, I've been doing that - unfortunately it seems to mean that an end user won't be able to change the font settings etc because they would need to edit the textbox properties and the sheet will be protected.

I tried to follow your Private Sub example, putting this in the code for VAT, but I have to run the code for it to work - do Private Subs only work for active X?

<CODE>Private Sub TextBox1_Change()
Sheets("Del1").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("VAT").Shapes("TextBox 1").TextFrame.Characters.Text
End Sub</CODE>

 

[justanotheruser]

I tried to change the line of code to:

<CODE> Sheets("Del1").Shapes("TextBox 2").TextFrame.Characters.Text = Shapes("TextBox 1").TextFrame.Characters.Text </CODE>

because it is in the actual sheet, but that didn't work either?

 

[John_w]

Yep, I've been doing that - unfortunately it seems to mean that an end user won't be able to change the font settings etc because they would need to edit the textbox properties and the sheet will be protected.

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.

I tried to follow your Private Sub example, putting this in the code for VAT, but I have to run the code for it to work - do Private Subs only work for active X?

<CODE>Private Sub TextBox1_Change()
Sheets("Del1").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("VAT").Shapes("TextBox 1").TextFrame.Characters.Text
End Sub</CODE>

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.