Auto_Open one time
 

I'm relatively new to VBA and I'm trying to write a macro that will
Auto_Open on the first try and place a chart on the excel sheet. I've
gotten Excel (v. 2003) to create and save the chart automatically when
you open the workbook, but the problem is that every time the workbook
is opened the chart is replicated. How can keep the chart from
replicating. In other words, how do I only open the chart
automatically upon opening the file one time? Any advice is greatly
appreciated

Auto_Open one time
 

Maybe you could just count how many charts are on that worksheet (is the chart
on a worksheet???).

If there's one, then you're done and get out?

Option Explicit
Sub auto_Open()

With Worksheets("sheet1") '<-- change this
If .ChartObjects.Count 0 Then
Exit Sub
End If
End With

'code to make the chart
End Sub


You could look for the chart name, too.

Option Explicit
Sub auto_Open()
Dim TestChartObj As ChartObject

With Worksheets("sheet1") '<-- change this
Set TestChartObj = Nothing
On Error Resume Next
Set TestChartObj = .ChartObjects("Chart 1")
On Error GoTo 0

If TestChartObj Is Nothing Then
'create the chart
MsgBox "create the chart here"
End If
End With
End Sub

If you ctrl-click on the chart, you can see the chartobject's name in the
namebox (to the left of the name box).


wrote:

I'm relatively new to VBA and I'm trying to write a macro that will
Auto_Open on the first try and place a chart on the excel sheet. I've
gotten Excel (v. 2003) to create and save the chart automatically when
you open the workbook, but the problem is that every time the workbook
is opened the chart is replicated. How can keep the chart from
replicating. In other words, how do I only open the chart
automatically upon opening the file one time? Any advice is greatly
appreciated

--

Dave Peterson

Auto_Open one time
 

Code your auto_open so's it looks for a flag in a cell.

The flag can be anything like a text string.........."qwerty"

If this flag is present, the auto-open will stop.

Private Sub Auto_Open()
If Sheets("Sheet1").Range("a1").Value < "qwerty" Then Exit Sub
MsgBox "do the stuff"
End Sub


Gord Dibben MS Excel MVP

On 24 May 2007 13:10:46 -0700, wrote:

I'm relatively new to VBA and I'm trying to write a macro that will
Auto_Open on the first try and place a chart on the excel sheet. I've
gotten Excel (v. 2003) to create and save the chart automatically when
you open the workbook, but the problem is that every time the workbook
is opened the chart is replicated. How can keep the chart from
replicating. In other words, how do I only open the chart
automatically upon opening the file one time? Any advice is greatly
appreciated

Auto_Open one time
 

On May 24, 5:14 pm, Dave Peterson wrote:
Maybe you could just count how many charts are on that worksheet (is the chart
on a worksheet???).

If there's one, then you're done and get out?

Option Explicit
Sub auto_Open()

With Worksheets("sheet1") '<-- change this
If .ChartObjects.Count 0 Then
Exit Sub
End If
End With

'code to make the chart
End Sub

You could look for the chart name, too.

Option Explicit
Sub auto_Open()
Dim TestChartObj As ChartObject

With Worksheets("sheet1") '<-- change this
Set TestChartObj = Nothing
On Error Resume Next
Set TestChartObj = .ChartObjects("Chart 1")
On Error GoTo 0

If TestChartObj Is Nothing Then
'create the chart
MsgBox "create the chart here"
End If
End With
End Sub

If you ctrl-click on the chart, you can see the chartobject's name in the
namebox (to the left of the name box).

wrote:

I'm relatively new to VBA and I'm trying to write a macro that will
Auto_Open on the first try and place a chart on the excel sheet. I've
gotten Excel (v. 2003) to create and save the chart automatically when
you open the workbook, but the problem is that every time the workbook
is opened the chart is replicated. How can keep the chart from
replicating. In other words, how do I only open the chart
automatically upon opening the file one time? Any advice is greatly
appreciated

--

Dave Peterson

I actually tried both of your Posts Dave and Gord. Thanks for all your
help I have successfully completed the task :)