Creating a "Smart" Autoshape
 

I am using an Excel file to manually display the overall status of
multiple projects. I have defined 6 Phases that is common to all the
projects, each project is a separate row in the spreadsheet and by
stretching a Rectangle Autoshape on each row I can easily give a visual
presentation of each project status. What I would like to do is add a
bit of intelligence to the auto shape - storing 6 separate strings of
text that I can display when double clicking on the status bar for a
given project. The data is static, by this it is simple text, no formulas.

While this is not a normal use for Excel it is simple and easy to share
with others. Any help or suggestions is appreciated.

Stan

Creating a "Smart" Autoshape
 

Instead of using AutoShapes and TextBoxes, you could enter the text in
cells. Then, use Conditional Formatting to colour the cells, and display
the text, when appropriate.

There are instructions for conditional formatting in Excel's Help, and he

http://www.contextures.com/xlCondFormat01.html

Stan wrote:

I am using an Excel file to manually display the overall status of
multiple projects. I have defined 6 Phases that is common to all the
projects, each project is a separate row in the spreadsheet and by
stretching a Rectangle Autoshape on each row I can easily give a visual
presentation of each project status. What I would like to do is add a
bit of intelligence to the auto shape - storing 6 separate strings of
text that I can display when double clicking on the status bar for a
given project. The data is static, by this it is simple text, no formulas.

While this is not a normal use for Excel it is simple and easy to share
with others. Any help or suggestions is appreciated.

Stan


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Creating a "Smart" Autoshape
 

Stan,

Place the following code in a regular module then right-click
one of your auto shapes and assign the code to it.
(click the shape to display text, click it again to remove text)

'-------------------------------------
Sub DisplayProjectStatus()
Dim strName As String
strName = Application.Caller
With ActiveSheet.Shapes(strName).TextFrame.Characters
If Len(.Text) Then
.Text = vbNullString
Else
.Text = "On Shedule"
End If
End With
End Sub
'-------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Stan" wrote in message
om...
I am using an Excel file to manually display the overall status of
multiple projects. I have defined 6 Phases that is common to all the
projects, each project is a separate row in the spreadsheet and by
stretching a Rectangle Autoshape on each row I can easily give a visual
presentation of each project status. What I would like to do is add a
bit of intelligence to the auto shape - storing 6 separate strings of
text that I can display when double clicking on the status bar for a
given project. The data is static, by this it is simple text, no formulas.
While this is not a normal use for Excel it is simple and easy to share
with others. Any help or suggestions is appreciated.
Stan

Creating a "Smart" Autoshape
 

Jim Cone wrote:
Stan,

Place the following code in a regular module then right-click
one of your auto shapes and assign the code to it.
(click the shape to display text, click it again to remove text)

'-------------------------------------
Sub DisplayProjectStatus()
Dim strName As String
strName = Application.Caller
With ActiveSheet.Shapes(strName).TextFrame.Characters
If Len(.Text) Then
.Text = vbNullString
Else
.Text = "On Shedule"
End If
End With
End Sub
'-------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Stan" wrote in message
om...

I am using an Excel file to manually display the overall status of
multiple projects. I have defined 6 Phases that is common to all the
projects, each project is a separate row in the spreadsheet and by
stretching a Rectangle Autoshape on each row I can easily give a visual
presentation of each project status. What I would like to do is add a
bit of intelligence to the auto shape - storing 6 separate strings of
text that I can display when double clicking on the status bar for a
given project. The data is static, by this it is simple text, no formulas.
While this is not a normal use for Excel it is simple and easy to share
with others. Any help or suggestions is appreciated.
Stan

Jim

This was great. Thanks for the help.

Stan