[RESOLVED] Excel Macro In Vb

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.

VB Code:

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

Add the following at the top of the code..

VB 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..

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 ...

Code:

---

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

---

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 :-)

Quote:

---

Originally Posted by dannymking

Add the following at the top of the code..

VB 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..

---