Prev: Sort bug on Excel 2007
Next: Copy filtered data (Values only)
From: BoRed79 on 29 Apr 2010 07:24
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.
From: Jacob Skaria on 29 Apr 2010 07:46
--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



--
Jacob (MVP - Excel)


"BoRed79" wrote:

> 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.
From: BoRed79 on 29 Apr 2010 08:22
Brilliant - the first option worked like a charm! Thanks.

"Jacob Skaria" wrote:

> --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
>
>
>
> --
> Jacob (MVP - Excel)
>
>
> "BoRed79" wrote:
>
> > 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.
 | 
Pages: 1
Prev: Sort bug on Excel 2007
Next: Copy filtered data (Values only)