Test for entries

[Sammy]

Hello all
The following macro is the final part of a routine that will creates a
chart.
What code would I need to insert that would test the activecell for
entries and if none print a message like "No data to Chart".

Thanks for any help.

Sammy


Sub MakeChart
Sheets("Build Chart").Select
ActiveCell.CurrentRegion.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
On Error Resume Next
ActiveChart.SeriesCollection(1).Name = "=""Forecast"""
ActiveChart.SeriesCollection(2).Name = "=""Returned"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "2003/4"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Categories"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue,
LegendKey:=False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
Call CoverRangeWithAChart
End Sub

 

[Dave Hawley]

Hi Sammy

How about

If IsEmpty(ActiveCell) Then
MsgBox "Current Active cell is empty", vbCritical
Exit Sub
End If



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Wouter]

Hi,

You can use

Sub MakeChart
Sheets("Build Chart").Select
Is IsEmpty(ActiveCell) Then
MsgBox "No Chartdata availeble",vbOkOnly, "Chart"
Exit Sub
End If
ActiveCell.CurrentRegion.Select
' And the rest of your code

You have to be sure that on this sheet all the cell containing the
data are selected prior to activating this procedure

Good Luck,

Wouter HM.

 

[Sammy]

Dave/Wouter
Thanks guys both worked great.
All the best.

Sammy