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?

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" wrote:

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?

Great. Thank you very much.

"JLatham" wrote:

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" wrote:

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?

Hope it all works out for you.

I didn't mention "how" it works. But you may have already figured that out.
When any of the sheets with a copy of the text box is selected/activated,
the code runs to update the text box on that sheet from the master. Since
you can't see the other sheets that you haven't selected (and probably have
outdated text box formatting on them) I figure you don't really care. Only
time that would probably matter is if you select a group of sheets that
include some that haven't been updated and print them. Then the not-updated
text boxes would be noticeable in the printout, if they are set to be printed.


"Bob Arnett" wrote:

Great. Thank you very much.

"JLatham" wrote:

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" wrote:

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?