[RESOLVED] Excel Macro In Vb

[Siddharth Rout]

CAN ANYONE HELP ME?

IN THE FOLLWING EXCEL MACRO, I WANT THE MACRO TO CHECK IF THERE IS ALREADY A SHEET BY THE NAME OF "AHTBARCHART" IF THERE IS THEN IT SHOULD DISPLAY A POPUP WINDOW "CHART ALREADY EXISTS" ELSE IT SHOULD GO AHEAD AND CREATE A CHART AS MENTIONED BELOW.

Sub AHTBARCHART()
'
' AHTBARCHART Macro
'
 
'
   Range("D3:D13,F3:F13").Select
   Range("F3").Activate
   Charts.Add
   ActiveChart.ChartType = xlColumnClustered
   ActiveChart.SetSourceData
Source:=Sheets("JANUARY").Range("D3:D13,F3:F13"), _
       PlotBy:=xlColumns
   [U]ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="AHTBARCHART"[/U]
   With ActiveChart
       .HasTitle = True
       .ChartTitle.Characters.Text = "AVERAGE AHT PER TEAM LEADER"
       .Axes(xlCategory, xlPrimary).HasTitle = True
       .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TEAM
LEADER"
       .Axes(xlValue, xlPrimary).HasTitle = True
       .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "AVERAGE
AHT"
   End With
   Sheets("JANUARY").Select
   Range("A1").Select
   Sheets("AHTBARCHART").Select
End Sub

[dannymking]

Add the following at the top of the code..

Dim i As integer
Dim SheetFound As Boolean
For i = 1 to Sheets.Count
  If Sheets(i).Name = "AHTBARCHART" Then 
    SheetFound
    Exit Do
  End If
Next i
If SheetFound Then
  Msgbox "your Message"
  Exit Sub
End If
'Otherwise contiue on..

[Webtest]

Here is another way to do it that does not require iterating through all the sheets, though any time saved would not likely be noticeable. This is just a template to show you the code flow ...

Option Explicit
Sub Macro1()
    Dim i As Integer
    
    On Error GoTo ERR_NO_SHEET
    i = ActiveWorkbook.Sheets("Sheet3").Index
    
    'Here is where processing resumes after the Error Handler is done
    On Error GoTo 0
    MsgBox "Sheet 3 Exists and will be used"
    Exit Sub
    
ERR_NO_SHEET:
    'Here is where you would create the new sheet
    MsgBox "There is no Sheet 3"
    Resume Next

End Sub

[Siddharth Rout]

THANKS DANNYMKING

YOUR CODE WAS A GREAT HELP. THOUGH I HAD TO MAKE A FEW CHANGES LIKE

1) SHEETFOUND= TRUE in the if-endif loop
2) 'EXIT FOR' INSTEAD OF Exit Do

and it worked like a miracle. thanks a ton once again :-)

Add the following at the top of the code..

Dim i As integer
Dim SheetFound As Boolean
For i = 1 to Sheets.Count
  If Sheets(i).Name = "AHTBARCHART" Then 
    SheetFound
    Exit Do
  End If
Next i
If SheetFound Then
  Msgbox "your Message"
  Exit Sub
End If
'Otherwise contiue on..