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

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 here:

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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