Insert Shape Based on Cell Changes

[BoRed79]

Hi.

I have a graph in Excel which is populated with data based upon a selection
from a drop down box. For some of the selections though there is no data and
an error message comes up and the graph does not display properly.

I want to (using VBA) have a command that executes if there is no data to
populate the graph and temporarily display a text box over the graph to say
data unavailable - that way the graph does not look untidy.

I have put together some code - which is not working - and wondered if
anyone can give me some advice to make it work and also to supress the error
message.

The code I have started is contained in the ThisWorkbook object and is as
follows:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

If Sheets("TIA").Range("A38") = 0 Then

If Sh.Name = "TIA" Then

Worksheets("TIA Analysis").Select

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 195, 18, 425,
268).Select
Selection.Characters.Text = "DATA UNAVAILABLE"
Selection.HorizontalAlignment = xlCenter
With Selection.Characters.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 28
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With

End If
End If

End Sub


Any help would be welcomed.

Thanks.

Liz.

 

[Jacob Skaria]

--Cant you set the visible property of the chart to false from Sheets("TIA")
worksheet_Change event referring to Range("A38") ...like

Worksheets("TIA Analysis").ChartObjects("Chart name").Visible = False

--If you really want to work on the shape object try the below code. You
dont need to select the sheet or the text box...Again make use of the
worksheet_Change event referring to Range("A38")


Dim sh As Shape

Set sh = Worksheets("TIA Analysis").Shapes.AddTextbox( _
msoTextOrientationHorizontal, 195, 18, 425, 268)
With sh.TextFrame
.Characters.Text = "No Data"
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
.Characters.Font.Name = "Arial"
.Characters.Font.Size = 28
.Characters.Font.ColorIndex = 3
End With

 

[BoRed79]

Brilliant - the first option worked like a charm! Thanks.