how to disable chart tab in Excel?

[Amit]

Hello all,

I'm have an active sheet with data in it. Also, there is a chart1 tab
which let user to see the graph of calculated data. How can I enable
and disable the chart tab in lower-left part of the screen?
It must be disable as long as the sheet is not puplated with data.

Thanks,
amit

 

[Guest]

Amit.

Try this on the code for the worksheet where the datatable is.

Change the constants at the start of the code for your requirments.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'assume data table in A2:B4
Const cszRange As String = "A2:B4"
Const cszChart As String = "Chart1"

Dim rCells As Range
Dim lCellsNr As Long

Set rCells = Me.Range(cszRange)
lCellsNr = rCells.Cells.Count

If Not (Intersect(Target, rCells) Is Nothing) Then
Charts(cszChart).Visible = _
WorksheetFunction.CountBlank(rCells) <> lCellsNr
End If
Set rCells = Nothing ' not really needed but better here.
End Sub