OK, start off as you've said: creating a "master" text box and setting it up,
including assigning the macro it will call. Copy it to the other sheets you
want it on.
You will need some worksheet event code to handle the updating. We'll use
the _Activate() event to make the other Text Boxes match the "master". Put
the code below into each worksheet that has a copy of the master text box on
it. To get it into the right place, go to the sheet(s) and right-click on
the name tab and choose [View Code] and copy and paste the code below into
the module.
You will need to change the name of the "master" sheet, which in the code is
"Sheet1". And you'll need to make sure that the names of the text boxes
referenced are correct. Note that there's one section of code that I've
commented out - it's the text alignment section; works in 2003, not in 2007.
So that's something I have to look at.
Private Sub Worksheet_Activate()
Dim masterTB As Shape
Dim sheetTB As Shape
Dim tLen As Integer
'assumes sheet with the "master" text box is named "Sheet1"
'change as required.
'also, the name of the specific text boxes will need to be
'correct for both sheets.
Set masterTB = Worksheets("Sheet1").Shapes("TextBox 1")
Set sheetTB = ActiveSheet.Shapes("TextBox 1")
'make the text the same
sheetTB.TextFrame.Characters.Text = _
masterTB.TextFrame.Characters.Text
tLen = Len(masterTB.TextFrame.Characters.Text)
'set the fill colors
sheetTB.Fill.BackColor = masterTB.Fill.BackColor
sheetTB.Fill.ForeColor = masterTB.Fill.ForeColor
'make sure the text characterstics are the same
With sheetTB.TextFrame.Characters(Start:=1, Length:=tLen).Font
.Name = masterTB.TextFrame.Characters.Font.Name
.FontStyle = masterTB.TextFrame.Characters.Font.FontStyle
.Size = masterTB.TextFrame.Characters.Font.Size
.Strikethrough = masterTB.TextFrame.Characters.Font.Strikethrough
.Superscript = masterTB.TextFrame.Characters.Font.Superscript
.Subscript = masterTB.TextFrame.Characters.Font.Subscript
.OutlineFont = masterTB.TextFrame.Characters.Font.OutlineFont
.Shadow = masterTB.TextFrame.Characters.Font.Shadow
.Underline = masterTB.TextFrame.Characters.Font.Underline
.ColorIndex = masterTB.TextFrame.Characters.Font.ColorIndex
End With
'set up the text alignment properties
'this works with 2003, not with 2007
' With sheetTB.TextFrame
' .HorizontalAlignment = masterTB.TextFrame.HorizontalAlignment
' .VerticalAlignment = masterTB.TextFrame.VerticalAlignment
' .ReadingOrder = masterTB.TextFrame.ReadingOrder
' .Orientation = masterTB.TextFrame.Orientation
' .AutoSize = masterTB.TextFrame.AutoSize
' End With
'and the line format
With sheetTB.Line
.Weight = masterTB.Line.Weight
.DashStyle = masterTB.Line.DashStyle
.Style = masterTB.Line.Style
.Transparency = masterTB.Line.Transparency
.Visible = masterTB.Line.Visible
.ForeColor.SchemeColor = _
masterTB.Line.ForeColor.SchemeColor
.BackColor = masterTB.Line.BackColor
End With
'and some size parameters
With sheetTB
.LockAspectRatio = _
masterTB.LockAspectRatio
.Height = masterTB.Height
.Width = masterTB.Width
End With
sheetTB.Left = masterTB.Left
sheetTB.Top = masterTB.Top
Set masterTB = Nothing
Set sheetTB = Nothing
End Sub
Bob Arnett said:
I have several worksheets (Excel 2007) and I want to put a shape on each
sheet that runs the same macro when clicked. I know I can copy and paste but
is there a way that it can be configured to update all the shapes if any
changes are made in the shape properties or position of one?
